Running Balance (Debit and Credit) in Access 2007

  • Thread starter Thread starter jkcansicio
  • Start date Start date
J

jkcansicio

Hello!

I just have a simple problem regarding running balance for debit and credit, and to figure it out see below:

ID Date Dr Amount Cr Amount Balance
1 01/01/14 1000 0 1000
2 01/02/14 0 500 500
3 01/03/14 0 200 300
4 01/05/14 8000 0 8300
5 01/11/14 0 1500 6800


QUESTION:
1. How can i get the running balance (the 3rd column)using the query?


Hope to receive feedback very soon.


Thank you very much!
JK
 
Hello!

I just have a simple problem regarding running balance for debit and credit, and to figure it out see below:

ID Date Dr Amount Cr Amount Balance
1 01/01/14 1000 0 1000
2 01/02/14 0 500 500
3 01/03/14 0 200 300
4 01/05/14 8000 0 8300
5 01/11/14 0 1500 6800


QUESTION:
1. How can i get the running balance (the 3rd column)using the query?


Hope to receive feedback very soon.


Thank you very much!
JK
Click to expand...

If you are looking for the last record (current balance) then:

SELECT TOP 1 [Date], [Balance]
FROM <table>
ORDER BY [Date] DESC;


Will return the most recent transaction date and balance.
 
Hello!

I just have a simple problem regarding running balance for debit and credit, and to figure it out see below:

ID Date Dr Amount Cr Amount Balance
1 01/01/14 1000 0 1000
2 01/02/14 0 500 500
3 01/03/14 0 200 300
4 01/05/14 8000 0 8300
5 01/11/14 0 1500 6800


QUESTION:
1. How can i get the running balance (the 3rd column)using the query?


Hope to receive feedback very soon.


Thank you very much!
JK
Click to expand...

Try:

SELECT [ID], [Date], [Dr Amount], [Cr Amount], DSUM("[Dr Amount] - [Cr
Amount]", "yourtablename", "[Date] <= " & [Date]) AS Balance
FROM yourtable
ORDER BY [Date];

Note that Date is a reserved wordl, for the builtin Date() function, and is a
bad choice of fieldname.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
After serious thinking (e-mail address removed) wrote :
Hello!

I just have a simple problem regarding running balance for debit and credit,
and to figure it out see below:

ID Date Dr Amount Cr Amount Balance
1 01/01/14 1000 0 1000
2 01/02/14 0 500 500
3 01/03/14 0 200 300
4 01/05/14 8000 0 8300
5 01/11/14 0 1500 6800


QUESTION:
1. How can i get the running balance (the 3rd column)using the query?


Hope to receive feedback very soon.


Thank you very much!
JK
Click to expand...

You have not described your scenario very succinctly, but assuming you
have a table (I called it tblYourTable) with 3 Columns Named TransDate,
DrAmount, and CrAmount, the Following query will do what you want.

SELECT drvYourTable.TransDate, drvYourTable.DrAmount,
drvYourTable.CrAmount,
(Select Sum(DrAmount - CrAmount)
From tblYourtable
Where tblYourtable.TransDate <= drvYourtable.TransDate
) AS Balance
FROM tblYourTable AS drvYourTable
ORDER BY drvYourTable.TransDate;

Query Results:

TransDate DrAmount CrAmount Balance
1/1/2014 $1,000.00 $0.00 $1,000.00
1/2/2014 $0.00 $500.00 $500.00
1/3/2014 $0.00 $200.00 $300.00
1/4/2014 $8,000.00 $0.00 $8,300.00
1/11/2014 $0.00 $1,500.00 $6,800.00
 
I performed it as same but when i create a report and i want that in the bottom of report to see the total of Dr ount,Cr Amount Or Balance also but there is an error come: ("Multi level Group-by is not allowed in subquery"). Plz help me to solve this problem.
 
Back
Top