Refining Queries  «Prev  Next»
Lesson 10 Using Totals queries
Objective Work with a Totals query to present group totals.

Using Access Totals Queries

Your standard Select query is great for displaying every record in a result set. There are times, however, that you just want to display aggregate data, such as summations or counts. A typical example might be when you need to total all the hours that have been spent on a particular project.
As with other types of queries, you will start with a Select query. The fields that you specify should also include the fields that you are going to want to display the aggregates of. After you pick the fields, you will turn the query into a Totals query by clicking on the Totals toolbar button

Totals toolbar button
Totals toolbar button

You will now be presented with a new row called the Total row. In the Total row, you will specify each of the fields as a Group By, which tells Access how to group the records.
The other items you can specify are aggregate functions. Aggregate function are how you perform a particular task, such as summing the values. You can perform the following aggregate functions:

FunctionDescription
SumSummation of the field.
AvgAverage value.
MinMinimum value.
MaxMaximum value.
CountCount the records for the group.
StDevStandard deviation.
VarVariance.
FirstFirst value of the field in the group.
LastLast value of the field in the group.
ExpressionThis can be a calculated value to display other information.
WhereIf you are using the field strictly for criteria purposes and don’t want it to show up, use this function.

In the next lesson, you will learn what Crosstab queries are and how to create them.

Creating Totals - Query

Click the exercise link below to get a good feel for creating a Totals query.
Creating Totals - Query