Running Sums in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Back
Top