Running Balance (Debit and Credit) in Access 2007

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
 
N

Norman Peelman

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

John W. Vinson

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
 
R

Ron Weiner

After serious thinking [email protected] 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
 
Top