Forum Discussion

Tony2021's avatar
Tony2021
Iron Contributor
Aug 20, 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 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

 

 

 

 

access

6 Replies

  • Tony2021's avatar
    Tony2021
    Iron Contributor
    Aug 23, 2022

    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.  

      • 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.

  • arnel_gp's avatar
    arnel_gp
    Iron Contributor
    Aug 22, 2022
    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.
    • Tony2021's avatar
      Tony2021
      Iron Contributor
      Aug 22, 2022

      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.

Resources