DBWebApplications 





Refining Access Queries  «Prev  Next»
Lesson 10 Using Totals queries
Objective Work with a Totals query to present group totals.
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.
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:
Function Description
Sum Summation of the field.
Avg Average value.
Min Minimum value.
Max Maximum value.
Count Count the records for the group.
StDev Standard deviation.
Var Variance.
First First value of the field in the group.
Last Last value of the field in the group.
Expression This can be a calculated value to display other information.
Where If 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