Running Balance (Debit and Credit) in Access 2007

[j***@gmail.com]

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

[Norman Peelman]

Post by j***@gmail.com
Hello!
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
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.

[John W. Vinson]

Post by j***@gmail.com
Hello!
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
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.

[Ron Weiner]

Post by j***@gmail.com
Hello!
I just have a simple problem regarding running balance for debit and credit,
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
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

[i***@dk-comp.com]

Post by Ron Weiner

Post by j***@gmail.com
Hello!
I just have a simple problem regarding running balance for debit and credit,
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
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;
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

Hi,

your query run perfectly on my issue, but I have a little problem, if the record have a multiple transaction on the same date, the calculation is misplace, something like this
id no faktur tgl transaksi nama perusahaan transaksi User masuk keluar balance
30 Qty Awal 01/01/2017 Qty Awal admin 5 0 5
31 4002854988 08/03/2017 PT. Astra Otoparts Beli 10 0 15
32 600000144 11/03/2017 MAXI KRYSTA mtr Jual Grosir angga 0 1 13
33 600000101 11/03/2017 Cash Jual Grosir asep 0 1 13
34 600000351 12/03/2017 Cash Jual Grosir mu 0 2 11
35 500000224 15/03/2017 Jual Eceran zai 0 1 10
36 600001044 18/03/2017 Cash Jual Grosir ANGGA 0 1 9
37 600001146 19/03/2017 Adi Motor Jual Grosir zai 0 1 8
38 500000391 20/03/2017 Jual Eceran angga 0 1 7
39 600001413 21/03/2017 Cash Jual Grosir asep 0 2 5
40 600001988 27/03/2017 Cash Jual Grosir MU 0 1 4
41 600002378 29/03/2017 CRM Jual Grosir mu 0 1 3
42 600002902 02/04/2017 OZI CIPETE Jual Grosir admin 0 2 1
43 600003255 05/04/2017 Cash Jual Grosir OZI 0 1 0

see id no 31 until 34, I tried so many different way, but the result is the same, any idea?

thx