ACC2: How to Create Running Totals in a Query

• Microsoft Access version 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article demonstrates two methods 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.

MORE INFORMATION

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 NWIND.MDB to create a running sum of the freight costs for each month in 1991. 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:

1. Open the sample database NWIND.MDB.

2. Create a new Select query and add the Orders table.

3. On the View menu, click Totals.

4. In first column of the QBE 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",[Order Date])
            Total: Group By
            Sort: Ascending
            Show: Yes
   
      The expression in the Field box displays and sorts the year portion of
      the Order Date field.
   
   

5. In the second column of the QBE 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",[Order Date])
            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.
   
   

6. In the third column of the QBE grid, type the following expression in the Field box, and make the following selections for the Total and Show boxes.

   NOTE: In the following expression, 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 expression.

        Field: RunTot: DSum("Freight","Orders","DatePart('m', _
           [Order Date])<=" & [AMonth] & " And  DatePart('yyyy', _
           [Order Date])<=" & [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.
   
   

7. In the fourth column of the QBE grid, type the following expression in the Field box, and make the following selections for the Total, Sort, and Show boxes:

         Field: FDate: Format([Order Date],"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.
   
   

8. In the fifth column of the QBE grid, type the following expression in the Field box, and make the following selections for the Total, Criteria, and Show boxes:

         Field: DatePart("yyyy",[Order Date])
            Total: Where
            Criteria: 1991
            Show: No
   
      The expression in the Field box filters the query's recordset to include
      only data from 1991.
   
   

9. Run the query. Note that the RunTot field displays the following records with a running sum:

         AYear   AMonth   RunTot    FDate
         --------------------------------
   
         1991    5        514.17    May
         1991    6        1225.75   Jun
         1991    7        2133.33   Jul
         1991    8        3041.5    Aug
         1991    9        4052.12   Sep
         1991    10       7345.04   Oct
         1991    11       8140.26   Nov
         1991    12       8933.32   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:

1. Open the sample database NWIND.MDB.

2. Create a new select query and add the Orders table.

3. On the View menu, click Totals.

4. In the first column of the QBE grid, add the following field to the Field box, and make the following selections for the Total and Show boxes:

         Field: EmpAlias: Employee ID
            Total: Group By
            Show: Yes
   
      This field groups data by Employee ID.
   
   

5. In the second column of the QBE 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.
   
   

6. In the third column of the QBE grid, type the following expression in the Field box, and make the following selections for the Total and Show boxes.

   NOTE: In the following expression, 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 expression.

         Field: RunTot: Format(DSum("Freight","Orders","[Employee ID]<=" _
            & [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 Employee ID is less than or equal to the current
      EmpAlias, and then formats the field in dollars.
   
   

7. Run the query. Note that the RunTot field displays the following records with a running sum:

         EmpAlias   SumOfFreight   RunTot
         ------------------------------------
   
         1          $10,450.91     $10,450.91
         2          $10,694.25     $21,145.16
         3          $13,347.13     $34,492.29
         4          $14,046.90     $48,539.19
         5          $4,352.02      $52,891.21
         6          $4,344.71      $57,235.92
         7          $7,967.62      $65,203.54
         8          $10,084.86     $75,288.40
         9          $3,918.26      $79,206.66
   
   

REFERENCES