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

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

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 (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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top