This article is also available as a PDF download.
Displaying subtotals, grand totals, and otherwise summarizing data is a common report feature. You can display a running total in a report simply by setting a property. A running total is a cumulative sum that evaluates the previous rows and the current row. In other words, each row's running total is equal to itself plus the previous total. Unfortunately, not every object handles a running total as easily as a report.
There's no built-in property or function for generating a running total in a query. For that, you'll need a rather complex expression in the form
(SELECT Sum(valuefield) AS Total
WHERE datasource.sortfield <= T1.sortfield) AS Total
FROM datasource AS T1
Table A explains the statement's arguments.
The fields you want the query to return
The field you're summing
The table (or query) that contains the values you're summing
A field of unique and ascending values
Although this expression works well, there's an unforgiving catch: The table must have a column of unique values in ascending order. That's sortfield in the above syntax statement. An AutoNumber field gets the job done, but a numeric field of unique values in ascending order will also work. Sorting by the actual values you want to total works only if those values are unique and happen to be in ascending order. You should avoid using the actual values for this reason. Even if they start out that way, it's unlikely they'll stay that way.
An example query
Let's build a query that generates running totals for the values in the table shown in Figure A. (OrderID is an AutoNumber field and OriginalValue is a Number field.) You can use any table, but it must have the two numeric fields we've already discussed: a field of values to sum and a field of unique values in ascending order.
With the select query in Design view, choose SQL View from the View button to open the SQL window. This window displays a query's SQL statement. (You can't enter this SQL statement via the QBE grid.) In the SQL window, enter the following SQL statement:
SELECT OrderID, OriginalValue,
(SELECT Sum(tblOriginalValues.OriginalValue) AS Total
WHERE tblOriginalValues.OrderID <= T1.OrderID) AS Total
FROM tblOriginalValues AS T1
When you run the query, the Total field returns a cumulative sum for the values in the OriginalValue field, as shown in Figure B.
Because the OrderID value (the AutoNumber field) is in ascending order, the WHERE clause's condition is always True, which forces a new sum for every record. This SQL statement works similarly to an aggregate domain function, but it's much faster.
You might have noticed that the AutoNumber values in the example's sortfield (OrderID) skips values. Gaps between values won't affect the results. In truth, it isn't necessary for sortfield to be in ascending order. The only absolute is that the field contain unique values. If sortfield isn't in ascending order, add an ORDER BY clause in the form:
ORDER BY T1.sortfield
Doing so will most likely change the running totals, so if order matters, rely on an AutoNumber field.
Finally, this solution will slow down when evaluating large amounts of data. The good news is that it should still perform faster than using an aggregate domain in either a query expression or a report or form control.
Generating a running total isn't intuitive to a query, but you can get the job done using a subquery. You'll need an extra numeric field with unique ascending values, which you can accomplish by simply adding an AutoNumber field to the table, if necessary. Once you've generated the running total in the query, you're free to view the list in any format you like.
Susan Sales Harkins is an independent consultant and the
author of hundreds of articles and several books on database technologies. Her
most recent book is Mastering SQL Server
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.
This got me on the right track, but I have a difficult situation. I need to create a graph of cumulative New & Closed Issues in an Issue Tracking Database. I have two queries that break down the data by year, month, and then count the number with status New & Closed, ie. [CountOf* Status] When I try to create a running total of the [CountOf* Status], for some reason, it just multiplies the number by 3. I can't get it to count correctly.
The way Susan given the solution is really greate. I appreciate that. What I understood from the example is that first of all access should take our table in a different name in the query grid. For that we will have to go to sql view and define a new name for the table by selecting atleast one field from the original table. Once that is acheved you can do the rest of the running total even in the grid view also. Ofcourse I again appreciate Susan for giving this tip. Thanks to Techworld & Susan
Have you had success using this query technique? What are some other workarounds you use on a regular basis?
on a big table, well not unless it's a convenient time for a coffee break. Beware of nulls in the data you are summing. In a more fully featured database you'd gave more options, cursors at a push, triggers by preference, both to another table which you'd join to. In short it works, but like most correlated sub queries, not very scalable.