Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

664

which the calculation is performed. In this case, the field you want to use as a filter is not a Group

By field.

For example, you may want to return sales totals only for the year 2012. Figure 18.24 shows qry-

AggregateByDate. This query is very similar to the previous example except that tblSales

has been added to include the SaleDate, and criteria is applied limiting SaleDate to the year

2012.

FIGURE 18.24

Specifying criteria for a Non-Aggregate field

Note
In the query you just completed, Access displays only those records for sales between 1/1/2012 and
12/31/2012, inclusive. All other records are discarded.

Access automatically turns off the Show box for fields using Where as the Total option. Access

knows these fields are used for filtering and are not expected to appear in the query results. If you

try to turn on the Show cell, Access displays an error message. If you need to see the field contents

in the datasheet, add a second copy of the field to the QBE pane. Only the field that has the Where

condition in the Total row is not shown.

Creating expressions for totals

In addition to choosing one of the Access totals from the drop-down list, you can create your own

aggregate expression based on any number of functions, such as Avg and Sum. Or you can base

your expression on a calculated field composed of several functions, or on a calculated field that is

based on several fields from different tables.