Query with a Running Total

[Solo7]

This should be easy! Right?

I have a series of dates with events that occured on those dates. Some events were extended, others were not how do I get a running total, cumulative total, for all records in the RunTotal column?

Opened Date Open Issues CountOfExtended NotExt RunTotal
5/21/2007 1 1 0 1
8/6/2007 1 1 0 2
10/8/2007 1 1 0 3
11/1/2007 1 1 0 4
11/8/2007 1 0 1 5
12/5/2007 1 0 1 6

Thanks for your help.

 

[ajetrumpet]

how do I get a running total, cumulative total, for all records in the RunTotal column?

Opened Date Open Issues CountOfExtended NotExt RunTotal
5/21/2007 1 1 0 1
8/6/2007 1 1 0 2
10/8/2007 1 1 0 3
11/1/2007 1 1 0 4
11/8/2007 1 0 1 5
12/5/2007 1 0 1 6

There is a Summation button on your toolbar for this. However, the totals that are populated by that are not permanent, but they are good for viewing purposes.

If you're wanting a sequential running total, that is much more difficult, and requires plenty of work...

 

[Pat Hartman]

Creating running sums in queries is extremely inefficient. If this data is destined for a report, that is where the running sum should be created since reports work very differently from queries and the sum is easy and efficient in that context.

To create running sums in queries, your query MUST include a unique identifier for each row. You need to create a query that joins the table to itself using a non-equi-join so you will need to work with the query in SQL view since QBE view cannot represent anything but equi-joins.

tblA Inner Join tblA1 On tblA.PK > tblA1.PK

This will allow you to sum all rows where the PK is less than the PK of the current row.

 

[ajetrumpet]

tblA Inner Join tblA1 On tblA.PK > tblA1.PK

This will allow you to sum all rows where the PK is less than the PK of the current row.

...Which is the exact same concept that is explained here:

http://support.microsoft.com/?kbid=208714

 

[Pat Hartman]

Except that the kb article uses DSum()s which work ONLY if you have a very small recordset. The join method is more efficient. If you want to create a running sum, the best way to do it is inside a report or inside a code loop if you need to write out the results to another recordset. Both the report and code loop process each record ONCE and only ONCE. The DSum() method and the non-equi-join both require reading the recordset once for every record in the recordset with a key less than the record key if that makes any sense to you. the resulting number of reads is enormous. For example using the DSum() method a thousand record recordset will require 999 + 998 + 997 + 996 ... +1 reads. I don't know the formulua but it adds up to significantly more than 1000 which all the report method or VBA method take.

 

[Cosmos75]

I fully agree with Pat Hartman's post about using a report rather than a query to do running totals. For more, see this thread - Really Urgent DSUM Problem

But if you are curious as to how to do it in a query:
Running Sum / Total / Count / Average in a query using a correlated subquery

 

[gemma-the-husky]

if you really need this in a query, save the frst query into a temp table (maketable query or use a predefined table) then use a recordset to generate your running totals in the temp table

this will require order of n reads, as opposed to n^2 reads, as explained above, and will be far more efficient