Forum Discussion
Sum records where ID is < current ID
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.
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.- 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!