Creating a Totals Query

A totals query includes a column that performs an aggregate operation—such as summing or averaging—on the values of a particular field. A totals query derives either a single value for the entire dynaset or several values for the records that have been grouped within the dynaset. Table 4.1 outlines the aggregate operations you can use for your totals queries.

Table 4.1. Aggregate Operations Available for Totals Queries

Operation

Purpose

Group By

Groups the records according to the unique values in the field.

Sum

Sums the values in the field.

Avg

Averages the values in the field.

Min

Returns the smallest value in the field.

Max

Returns the largest value in the field.

Count

Counts the number of values in the field.

StDev

Calculates the standard deviation of the values in the field.

Var

Calculates the variance of the values in the field.

First

Returns the first value in the field.

Last

Returns the last value in the field.

Expression

Returns a custom total based on an expression in a calculated column.

Where

Tells Access to use the field’s criteria to filter the records before calculating the totals.