kaysoo
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
=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:
Open in new window
When you add the Group By and Sum, this would reduce toOpen in new window
HTHDale