Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

658

5. Select Avg, Min, and Max in the Total row of the three RetailPrice fields.

Your query should look like Figure 18.15.

FIGURE 18.15

An aggregate query against records in tblProducts

This query calculates the total number of records in tblProducts as well as the average, mini-

mum, and maximum price for all products.

Name the query qryProductAggregateFunctions.

Note
The Count option of the Total cell can be performed against any field in the table (or query). However, Count
excludes records that have a null value in the field you select. Therefore, you may want to count the table’s
primary key field (like ProductID) because the primary key cannot have any null values.

This query’s results are shown in Figure 18.16. Notice that the recordset has only one record.

This record specifies the count, average, minimum, and maximum value for all products (regard-

less of type) in tblProducts. The query in Figure 18.16 (qryProductAggregateFunc-

tions) is included in the Chapter18.accdb example database.

Note
Access creates default column headings for all total fields in a totals datasheet, as shown in Figure 18.16. The
heading is a product of the total option and the field name. You can change a column heading name to some-
thing more appropriate by renaming the field in the QBE pane of the Design window as described in the sec-
tion titled “Renaming fields in queries” earlier in this chapter.

This query was performed against all records in a table or query, and the query result consists of

only one record.