Microsoft Access 2010 Bible

Chapter 18: Advanced Access Query Techniques

663

Note
The query design in Figure 18.22 does not precisely match the design of qryFilteredGroupBy in the
Chapter18.accdb example database. The difference occurred when the query in Figure 18.22 was closed
and saved in the Chapter18.accdb database. The Query Optimizer rearranged the query’s design so that
the Sales total is calculated by applying the Sum function directly to the product of Quantity * RetailPrice. The
records returned by the query are the same, regardless of how the Sum function appears in the query’s design.

Specifying criteria for an Aggregate Total field

At times, you may want a query to calculate aggregate totals first and then display only those totals

from the aggregate calculations that meet a specified criterion. More specifically, you may want to

perform aggregate calculations against all records but return only records meeting some criteria. In

effect, you’re saying “I won’t know which records I want to see until they’re all totaled first. Then I

want to see only those records that meet a particular criterion in my results.”

For example, you may want a query to find the total sales by category, but only for categories with

more than $1,000 in sales. This query should look like the one shown in Figure 18.23. Notice that

> 2000 appears in the field summing product sales.

FIGURE 18.23

A query that filters returned records after grouping

The query shown in Figure 18.23 (qryAggregateWithCriteria) is included in the

Chapter18.accdb database.

Specifying criteria for a Non-Aggregate Total field

The preceding section showed you how to limit the records after performing the calculations

against total fields. You also can specify that you want Access to limit the records based on a total

field before performing total calculations. In other words, you can limit the range of records against