Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Dec 03, 2021

Balance Report

Hello,

 

I am trying to develop a query that shows a balance. 

This is not as easy as it is in Excel since a formula can grab the ending balance from the line above. 

How could I show a balance?  

 

I have a link to the file.  I am getting an error when I try to upload to this website. 

https://drive.google.com/file/d/1AlwkezdixnHyIgRZR7x5rm5VMhnRItCw/view?usp=sharing

 

 

thank you

access
  • arnel_gp's avatar
    arnel_gp
    Dec 04, 2021
    you can replace your SQL query (SQL View )with this:

    SELECT tblLetterOfCredit.Amount, tblLCAmends.AmendAmt, tblLCAmends.DateApprovedBank, [Amount]+DSum("AmendAmt","tblLCAmends","letterOfCreditID=" & [tblLetterOfCredit].[LCID] & " And [ID] <= " & [tblLCAmends].[ID]) AS Balance, tblLetterOfCredit.DateOfExpirey
    FROM tblLetterOfCredit INNER JOIN tblLCAmends ON tblLetterOfCredit.LCID = tblLCAmends.letterofcreditID
    ORDER BY tblLCAmends.ID;

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Dec 03, 2021
    It would help a lot if we had a bit more information about what is supposed to be involved in this calculation, what you want a balance of, etc. I know we could invest some time exploring the accdb and make some educated guesses. It's probably better, though, if you explain it instead.
    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Dec 03, 2021
      Hi George,

      I will explain.
      1. Amount + Amendment1 = Balance1
      2. Balance1 + Amendment2 = Balance2
      3. Balance2 + Amendment3 = Balance3
      to N....

      its not so straightforward to use a balance from the record above. Excel is much easier.
      I think the solution is to refer to the [ID] in the previous Balance somehow and use that as the opening balance for the next record.

      In the linked db above, you can see I have amounts and a date associated with those amounts and so on that date, the amendment was done.
      • arnel_gp's avatar
        arnel_gp
        Steel Contributor
        Dec 04, 2021
        you can replace your SQL query (SQL View )with this:

        SELECT tblLetterOfCredit.Amount, tblLCAmends.AmendAmt, tblLCAmends.DateApprovedBank, [Amount]+DSum("AmendAmt","tblLCAmends","letterOfCreditID=" & [tblLetterOfCredit].[LCID] & " And [ID] <= " & [tblLCAmends].[ID]) AS Balance, tblLetterOfCredit.DateOfExpirey
        FROM tblLetterOfCredit INNER JOIN tblLCAmends ON tblLetterOfCredit.LCID = tblLCAmends.letterofcreditID
        ORDER BY tblLCAmends.ID;

Resources