Running Sums in a query

[Guest]

Is there any way to write a query with a running sum - for example a checking
account balance which would show the running balance when you queried it
based on deposit and withdrawal transactions in the checking account? I have
only been able to do running sums in reports. I'm not as familiar with
access as I would like to be so please explain in detail for me if this can
be done and how.

Thanks so much to all

 

[Allen Browne]

It's slow and clumsy to do this in a query, and it only works if you know
the sort order for sure.

Simplest is a DSum(). This example assumes a table named Table1, with
fields:
ID primary key, and the sort order of the query;
Credit Currency
Debit Currency
You would then type this into a fresh column of the Field row in your query:
DSum("[Credit] - [Debit]", "Table1", "ID <= " & [ID])

*Much* more efficient is a subquery, but the results are read only so not
suited for a form where you need to edit, and as the source for a report you
can run into problems with "Multi-level group by not permitted" error. You
type something like this into the Field row of your query:
(SELECT Sum([Credit] - Debit]) AS Balance
FROM Table1 AS Dupe WHERE Dupe.ID <= Table1.ID )

Of course, both approaches fail if you feed this into a form where the user
can change the sort order.