Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

662

Changing the order of the Group By fields in a totals query enables you to look at data in new and

creative ways.

Specifying criteria for a total query

In addition to grouping records for total queries, criteria to limit the records that will be processed

or displayed in a total calculation can be specified. When you’re specifying record criteria in total

queries, several options are available:

l Group By

l (^) Aggregate Total
l Non-Aggregate Total
Using a combination of these criteria types limits the scope of data returned by the query.
Specifying criteria for a Group By field
Criteria can be added to Group By fields in a query. These criteria limit the records included in the
Group By field so that the calculations performed in other fields include only the filtered records.
For instance, assume the sales manager is interested in the total sales to CT, NH, and NY, but not
any other states. Figure 18.22 shows this query’s design (see qryFilteredGroupBy in the database
Chapter18.accdb).
FIGURE 18.22
Specifying criteria in a Group By field
Without the criteria in the Group By field, qryFilteredGroupBy returns a record for every
state.