Forum Discussion
Solved
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? ...
- 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 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.
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.
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;
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!