Access Query to calculate the running balance from Credit and Debit field

kaysoo used Ask the Experts™

Can anyone show me how to create a query in order to achieve the result of "balance field" in access query?
I hv attached a print screen and sample database for formula input.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Like this:

SELECT Table1.Date, Table1.description, Table1.debit,, Table1.balance AS BalanceDemo, 
(Select Sum(Nz(T.Credit,0)-Nz(T.Debit,0)) From Table1 As T Where T.Date <= Table1.Date) AS Balance
FROM Table1
ORDER BY Table1.Date;

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

if you are going to use this in a report , and not a form, then you can simply add a textbox to the report.  Change the ControlSource to:

=NZ([Credit], 0) - NZ([Debit], 0)

Then you would set the Running sum property of the textbox to "Over All"

Another method, slightly different from Gustav's, which I find to generally run quicker is to use something like:

SELECT T1.DateField, Sum(Val(NZ(T2.Credit, 0)) - Val(NZ(T2.Debit, 0))) as RunningBalance
FROM (SELECT Distinct [DateField] FROM yourTable) as T1
LEFT JOIN yourTable as T2 ON T1.DateField >= T2.DateField
GROUP By T1.DateField

The non-equi join cannot be visualized in the query design grid, so I generally create it in SQL, but you could create the general query in the designer, then switch over to SQL view and make the = into a >=.  If you didn't do the GroupBy and Sum, what you would get is a set of unique date values in T1, and that would be paired with all records in yourTable (T2) with dates which are <= the T1.Date.  which might look like:
T1.DateField    T2.DateField     Credit          Debit
1/1/17               1/1/17          5
1/2/17               1/1/17          5                 
1/2/17               1/2/17         25             10
1/3/17               1/1/17          5                
1/3/17               1/2/17         25             10
1/3/17               1/3/17         20             15

Open in new window

When you add the Group By and Sum, this would reduce to
T1.DateField            Balance
1/1/17                     5
1/2/17                    20
1/3/17                    25

Open in new window


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial