Data Management

How do I... Generate a running total in an Access query?

There's no built-in way to generate a running total in your Access queries, but you can still get the job done if you set up your table correctly and build the right expression. Database expert Susan Sales Harkins explains the technique.

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.

The expression

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 fieldlist,
(SELECT Sum(valuefield) AS Total
FROM datasource
WHERE datasource.sortfield <= T1.sortfield) AS Total
FROM datasource AS T1

Table A explains the statement's arguments.

Table A

Argument

Explanation

fieldlist

The fields you want the query to return

valuefield

The field you're summing

datasource

The table (or query) that contains the values you're summing

sortfield

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.

Figure A

 

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

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.

Worth noting

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.

Keep totaling

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 Express, with Mike Gunderloy, published by Sybex. Other recent books are Automating Microsoft Access 2003 with VBA and Upgrader's Guide to Microsoft office System 2003. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@setel.com.

About

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.

8 comments
maggioant
maggioant

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.

muraliget
muraliget

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

lygh18
lygh18

How can on Database query select one Field

michaelvdnest
michaelvdnest

Use the RunningSum property of a textbox in a report.

NOW LEFT TR
NOW LEFT TR

Much more simple, even on a large table.

JodyGilbert
JodyGilbert

Have you had success using this query technique? What are some other workarounds you use on a regular basis?

Tony Hopkinson
Tony Hopkinson

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.

Editor's Picks