The information in this article applies to:
- Microsoft Access versions 7.0, 97
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.
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
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 select 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.
- 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.
- Run the query.
In Microsoft Access 97, note that the RunTot field displays the following
records with a running sum:
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:
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:
- Open the sample database Northwind.mdb.
- Create a new select query and add the Orders table.
- On the View menu, click Totals.
- 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.
- 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.
- 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.
- Run the query. Note that the RunTot field displays the following records
with a running sum:
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
REFERENCES
For more information about totals queries, search the Help Index for
"queries, calculations," and then "Perform Calculations in a Query" or ask
the Microsoft Access 97 Office Assistant.
For more information about creating a running totals query, please see the
following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q132134
TITLE : ACC2: How to Create Running Totals in a Query
|