SOLVED

# Balance Report

Super Contributor

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

thank you

4 Replies

# Re: Balance Report

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.

# Re: Balance Report

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

# Re: Balance Report

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;

# Re: Balance Report

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