SOLVED

Balance Report

Super Contributor

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

4 Replies
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.
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.
best response confirmed by Tony2021 (Super Contributor)
Solution
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;

Hi Arnel, that was perfect. Exactly what I was looking for. thank you so much!