Running Balance (Debit and Credit) in Access 2007

[jkcan...@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]

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.


--
Norman
Registered Linux user #461062
AMD64X2 6400+ Ubuntu 10.04 64bit

[John W. Vinson]

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

[Ron Weiner]

After serious thinking jkcan...@gmail.com wrote :

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

[irwan...@dk-comp.com]

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