Microsoft KB Archive/138911

= ACC: How to Create Running Totals in a Query (95/97) =

Article ID: 138911

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q138911



SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article demonstrates two methods that you can use to create a running totals query; that is, the total for each record is a summation of that record and any previous records. This type of query is useful for displaying cumulative totals over a group of records (or over a period of time) in a graph or report.

NOTE: A demonstration of the technique used in this article can be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

182568 ACC97: Microsoft Access 97 Sample Queries Available in Download Center



Method 1
The first method uses a DSum function and criteria in a query to create a running sum over time. The DSum function sums the current record and any previous records. When the query moves to the next record, the DSum function runs again and updates the cumulative total.

The following sample query uses the Orders table from the sample database Northwind.mdb to create a running sum of the freight costs for each month in 1994. The sample data is limited to one year for performance reasons. Because the DSum function runs once for every record in the query, it may take several seconds (depending on the speed of your computer) for the query to finish processing. To create and run this query, follow these steps:

 Open the sample database Northwind.mdb. Create a new query and add the Orders table. On the View menu, click Totals. In first column of the query design grid, type the following expression in the Field box, and make the following selections for the Total, Sort, and Show boxes:

Field: AYear: DatePart("yyyy",[OrderDate]) Total: Group By Sort: Ascending Show: Yes

The expression in the Field box displays and sorts the year portion of the OrderDate field. In the second column of the query design grid, type the following expression in the Field box, and make the following selections for the Total, Sort, and Show boxes:

Field: AMonth: DatePart("m",[OrderDate]) Total: Group By Sort: Ascending Show: Yes

The expression in the Field box sorts and displays the month portion of the Order Date field as an integer value from 1 to 12. In the third column of the query design grid, type the following expression in the Field box, and make the following selections for the Total and Show boxes. NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example.

Field: RunTot: DSum("Freight","Orders","DatePart('m', _ [OrderDate])<=" & [AMonth] & " And DatePart('yyyy', _ [OrderDate])<=" & [AYear] & "") Total: Expression Show: Yes

The expression in the Field box uses the DSum function to sum the Freight field when the values in both the AMonth and the AYear fields are less than or equal to the current record that the query is processing. In the fourth column of the query design grid, type the following expression in the Field box, and make the following selections for the Total, Sort, and Show boxes:

Field: FDate: Format([OrderDate],"mmm") Total: Group By Sort: Ascending Show: Yes

The expression in the Field box displays each month in a textual format, such a Jan, Feb, Mar, and so on.</li> In the fifth column of the query design grid, type the following expression in the Field box, and make the following selections for the Total, Criteria, and Show boxes:

Field: DatePart("yyyy",[OrderDate]) Total: Where Criteria: 1994 Show: No

The expression in the Field box filters the query's recordset to include only data from 1994.</li> Run the query.</li></ol>

In Microsoft Access 97, note that the RunTot field displays the following records with a running sum:

<pre class="fixed_text">    Ayear     Amonth     RunTot     Fdate -    1994      8           1288.18   Aug 1994     9           2685.35   Sep 1994     10          3808.83   Oct 1994     11          5219.05   Nov 1994     12          7481.28   Dec

In Microsoft Access version 7.0, note that the RunTot field displays the following records with a running sum:

<pre class="fixed_text">    Ayear     Amonth     RunTot     Fdate -    1994      1          1936.65    Jan 1994     2          3452.52    Feb 1994     3          5479.33    Mar 1994     4          8607.74    Apr 1994     5          12094.67   May 1994     6          13859.39   Jun 1994     7          17729      Jul 1994     8          21992.58   Aug 1994     9          27477.79   Sep 1994     10         31767.05   Oct 1994     11         36681.15   Nov 1994     12         42894.37   Dec

Method 2
The second method uses a totals query with a DSum function to create a running total over a group.

The following sample query uses the Orders table to sum freight costs per employee as well as to calculate a running sum of the freight. To create and run the query, follow these steps:

<ol> Open the sample database Northwind.mdb.</li> Create a new select query and add the Orders table.</li> On the View menu, click Totals.</li> In the first column of the query design grid, add the following field to the Field box, and make the following selections for the Total and Show boxes:

Field: EmpAlias: EmployeeID Total: Group By Show: Yes

This field groups data by EmployeeID.</li> In the second column of the query design grid, add the following field to the Field box, and make the following selections for the Total and Show boxes:

Field: Freight Total: Sum Show: Yes

This field sums the freight data.</li> In the third column of the query design grid, type the following expression in the Field box, and make the following selections for the Total and Show boxes. NOTE: In the following example, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example.

Field: RunTot: Format(DSum("Freight","Orders","[EmployeeID]<=" _ & [EmpAlias] & ""),"$0,000.00") Total: Expression Show: Yes

The expression in the Field box uses a DSum function to sum the Freight field when the EmployeeID is less than or equal to the current EmpAlias, and then formats the field in dollars.</li>  Run the query. Note that the RunTot field displays the following records with a running sum:

<pre class="fixed_text">    Employee            SumOfFreight   RunTot -    Davolio, Nancy      $8,836.64      $8,836.64 Fuller, Andrew     $8,696.41      $17,533.05 Leverling,Janet    $10,884.74     $28,417.79 Peacock, Margaret  $11,346.14     $39,763.93 Buchanan, Steven   $3,918.71      $43,682.64 Suyama, Michael    $3,780.47      $47,463.11 King, Robert       $6,665.44      $54,128.55 Callahan, Laura    $7,487.88      $61,616.43 Dodsworth, Anne    $3,326.26      $64,942.69 </li></ol>

<div class="references_section">