Microsoft Access 2010 Bible

Chapter 18: Advanced Access Query Techniques

655

The aggregates options can be divided into four distinct categories: Group By, Aggregate,

Expression, and Query Criteria. Table 18.3 lists each category, its number of Total options, and its

purpose.

TABLE 18.3

Four Types of Total Options

Category Purpose of Operator

Group By Groups common records together. Access performs aggregate calculations against the

groups.

Aggregate Specifies a mathematical or selection operation to perform against a field. There are nine

different aggregate functions in Access 2010.

Expression Groups several total operators together and performs the group totals.

Query Criteria Filters records before performing an aggregate calculation against a field.

The following sections provide details about the options available in each aggregation options.

Group By

Totals queries can get complicated because of the variety of options and functions that may be

used in a query. In all, there are nine different aggregate functions that may be applied to data in

the query, and three other options that determine how the query works.

One of the fundamental operations in a totals query is Group By, which is applied to the field(s)

used to group records in the underlying tables. For instance, consider a simple totals query that

counts the number of orders placed by each customer. In this query, you want to group by the

customer name and apply the Count aggregate function to the grouped records.

You use Group By to specify fields used to define the grouping applied to the other fields in the

query. For example, in Figure 18.14, the Category field is set to Group By, which means that all

the product records are grouped by the category field (all cars in one group, trucks in another, and

so on) and the Count function is applied to each group. Group By is the default for fields as they

are added to the QBE grid in a totals query. I discuss Group By in more detail in the “Specifying

criteria for a Group By field” section, later in this chapter.

The word aggregate implies gathering together a mass (a group or series) of objects and working on the

group of objects as a single entity. Therefore, an aggregate function takes a group of records and per-

forms a mathematical operation over the entire group. The mathematical operation can be a count or a

complex expression you specify.

What is an aggregate function?