Sum records where ID is < current ID

[Tony2021]

Experts,

I need to subtract:

tblFXRollsChild.Rate  I have a datasheet name of "Rate C",

from

tblFXRollsChild.Rate

where 

”IDFXParent=” & [tblFXRollsChild].[IDFXParent]

And (this is the hard part)

second condition:

[tblFXRollsChild].[IDRollsPK] is < & IDRollsPK 

I dont know if the bold is correct though. In simple terms It would be like saying is <Me.IDRollsPK but I know you cant use Me. in this case. 

here is my SQL:

tblFXRollsChild.Rate is the field that is named "Rate C" in above pic.  I have a datahseet name to overide the formal name of the field

SELECT tblFXParent.DateEntered, tblFXParent.IDFXParent, tblFXRollsChild.DateOfTx, tblFXRollsChild.AmountRoll, tblFXRollsChild.Rate, 
FROM tblFXParent LEFT JOIN tblFXRollsChild ON tblFXParent.IDFXParent = tblFXRollsChild.IDParentfk;

let me know if its not clear.  

thank you very much

[#2]

it is unclear what you want.
you need to subract tblFXRollsChild.Rate from tblFXRollsChild.Rate on same IDFXParent and
IDRollsPK is less than or equal?

it is best to show some data from both parent and child table.
then show the expected result.

[#3]

Hi Arnel, yes, that is exactly what I am looking for.
So, in the example above, the math would be:
1.240944 - 1.244714 for IDRollsPk = 36 on Me.IDFXParent
1.244714 - 1.233714 for IDRollsPk = 17 on Me.IDFXParent
I know I cant use Me. though.

Also, FYI: tblFXRollsChild.Rate is the field that is named "Rate C" in above pic. I am using a datasheet name in the display so could be slightly confusing since there is no field that is named "Rate C".

let me know if still not clear. thank you sir.

[#4]

@Tony2021 

Hi Arnel,

Please see attached sample database and excel file.  The excel is probably easier to understand what I am after. In Access there are 2 tables and 1 query pared down to only what is necessary.  I filtered it for ID 18 to simplify display.  One thing I noticed was that the first record on tblFXParent.IDFXParent would be a different calculation since its the first record.  In that case, the calculation would be tblFXRollsChild.Rate - tblFXParent.Rate. For all the other records where tblFXParent.IDFXParent = me.tblFXParent.IDFXParent  the math is tblFXRollsChild.Rate - tblFXRollsChild.Rate where the tblFXRollsChild.IDRollsPK is <Me.tblFXRollsChild.IDRollsPK)

Apologies but putting it in text is probably making it more difficult to understand.  

here is a screen shot of the access query (filtered): 

here is a screen shot of excel with trace precedents:

I have attached 2 files:

1 - Excel (this is easier to understand)

2 - Access 

I think if you view the excel file formulas it will be clear.

Let me know if you have any questions. 

Grateful for the help.  

[#5 · Accepted solution]

@Tony2021 

i made some small queries (numbered).

see 4_FinalQuery as your query

[#6]

I had no idea it would be that complicated.
I thought it would be something similar to this:
Nz(DSum("fieldName","tblName","ID=" & [tblName2].[ID2] & " And [ID] < " & Nz([tblName2].[ID],0)),0)

but I guess that since the first record is a different formula you cant use something similar to above. 

anyways, thank you once again sir. It looks great! Will circle back if any questions.

[#7]

I now see that you do have a similar formula like above:
PrevRate: (SELECT TOP 1 RATE FROM tblFxRollsChild AS T WHERE T.IDParentfk = tblFXRollsChild.IDParentFk AND T.IDRollsPK < tblFXRollsChild.IDRollsPK Order By IDRollsPK DESC)

very complicated indeed. thanks again!