<< Click here to display Table of Contents >> Navigation: Chapter 18: Working with Queries and Record Sources:

Adding totals to queries

Contents

You can add a Total row to your query by clicking the button on the application toolbar.

toolbar_tooltip_sample_totals

After you click this toolbar button, a Total row will appear in your query.

query_totals_row

If you click in a field in the Total row you will be presented with a drop-down list of options:

query_totals_row_selections

The options available in this drop-down list and their uses are outlined in the following table:

Sum	Retrieves the total of the values in a field.
Avg	Retrieves the average of the values in a field.
Min	Retrieves the lowest value in a field.
Max	Retrieves the highest value in a field.
Count	Retrieves the number of values in a field, not counting Null (blank) values.
StDev	Retrieves the standard deviation of the values in a field.
StDevP	Retrieves the standard deviation/population of the values in a field.
Var	Retrieves the variance of the values in a field.
VarP	Retrieves the variance/population of the values in a field.
First	Retrieves the first entry that matches the criteria listed in the function.
Last	Retrieves the last entry that matches the criteria listed in the function.
Group By	Defines the group for which you want to perform the calculations.
Where	Specifies criteria for a field you aren't using to define groupings. If you select this option for a field, the application will hide that field in the query results by disabling the Show checkbox.

For example, if you were working with a query that contained shipping data (with one field for the ship destination country and another field for the ship date), you could use this feature to retrieve the latest ship dates for each country. This could be done by setting up a query as such:

query_totals_row_selections_2

Your query results would then display each unique ShipCountry value in the first column, then the latest ship date value for each country in the LastOfShippedDate column:

query_totals_print_preview

Note that you can remove the Total row from your query by clicking the button again.