Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Aug 19, 2022
Solved

Sum records where ID is < current ID

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...
access
Tony2021's avatar
Tony2021
Iron Contributor
Aug 23, 2022

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.

  • Tony2021's avatar
    Tony2021
    Iron Contributor
    Aug 23, 2022
    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!

Resources