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

 

[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

 

[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