Solved I need running balance including add and less in the report field (1 Viewer)

[Khurramshaikh47]

Hi,
I need help calculating a running balance in a report field. Where the debit will accumulate in the balance and the credit will be subtracted from it.
In the image below my report is getting the "CASH IN HAND" transactions from General Journal and showing the balance from there.

I also want to show you these transactions in General Journal Query to more clearify my question, please see the query below which is the source of my report:

The output of the balance field in my report that I need is:

Kindly guide me on how can I calculate my desired values in my report's balance field.

Thank you.

 

[arnelgp]

something like:

SELECT VoucherDate, VoucherNo, Account, IIF(Debit<>0, "Cash Received", "Paid") As Narration,
Debit, Credit, (SELECT SUM([Debit]-[Credit]) From yourTable AS T Where T.Account = yourTable.Account And T.ID <= yourTable.ID) As Balance
From yourTable Where Account="Cash In Hand" Order By VoucherDate, ID;

the Total you can calculate on your report.

 

[Khurramshaikh47]

something like:

SELECT VoucherDate, VoucherNo, Account, IIF(Debit<>0, "Cash Received", "Paid") As Narration,
Debit, Credit, (SELECT SUM([Debit]-[Credit]) From yourTable AS T Where T.Account = yourTable.Account And T.ID <= yourTable.ID) As Balance
From yourTable Where Account="Cash In Hand" Order By VoucherDate, ID;

the Total you can calculate on your report.

Dear Arnel thank you for your response.
The error is appearing:

Also the Cash Received and Paid etc are just an example of narrations. Narration can be contained in the whole sentence.

 

[arnelgp]

can you upload a sample data with your journal?

 

[Khurramshaikh47]

Hi Arnel I solved this problem but another problem came up with it. I have calculated the running balance with the help of a subquery, and it also appears in the report.

But now when I am trying to get its sum

in the report I am getting an error.

Please guide me about it.

 

[Khurramshaikh47]

can you upload a sample data with your journal?

Yes I can

 

[Khurramshaikh47]

Arnel Please check the Sample DB

 

[arnelgp]

which Report and which Query?

 

[Khurramshaikh47]

which Report and which Query?

Report: AccountLedgerReport
AccountLedger

Use the following Dates and Account Name: to check

 

[arnelgp]

to prevent the error, i made another query (AcctLedgerForReport) from AccountLedger.
this new query is the recordsource of the report.

i also made total query (AccountLedger0Total), on the total textbox i just Dlookup() the value on TotalDb, TotalCr of this query.
the balance, just subtract the two textbox.

 

[Pat Hartman]

If you add the credits and debits together so you have only one column (you can make it very tiny and hide it on the report. You don't need any code or queries, you can use the RunningSum property of a control to show the RunningSum on the report.

 

[Khurramshaikh47]

to prevent the error, i made another query (AcctLedgerForReport) from AccountLedger.
this new query is the recordsource of the report.

i also made total query (AccountLedger0Total), on the total textbox i just Dlookup() the value on TotalDb, TotalCr of this query.
the balance, just subtract the two textbox.

Thank you so much arnel. Its solved.