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

Hi,

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.

tq
QueryDiagram.jpg
Sample.mdb
Gustav Brock

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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``````
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``````
HTH
Dale