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
 
Ad

Advertisements

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
 
Ad

Advertisements

Joined
May 25, 2018
Messages
1
Reaction score
0
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.
 

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

Similar Threads

Running Balance in Access 2007 5
Query to sum debits and credits 1
URGENT - Need to match debits and credits 1
Running Balance 2
Running Balance 5
running balance 17
Running Balance 1
Running Balance 1

Top