Microsoft Access 2010 Bible

Chapter 18: Advanced Access Query Techniques

657

TABLE 18.4

Aggregate Options of the Total Row

Option Finds Field Type Support

Count Count of non-null values in a field AutoNumber, Number, Currency, Date/Time, Yes/No,

Text, Memo, OLE object

Sum Total of values in a field Number, Currency, Date/Time, Yes/No

Avg Average of values in a field Number, Currency, Date/Time, Yes/No

Max Highest value in a field Number, Currency, Date/Time, Yes/No, Text

Min Lowest value in a field Number, Currency, Date/Time, Yes/No, Text

StDev Standard deviation of values in a field Number, Currency, Date/Time, Yes/No

Var Population variance of values in a field Number, Currency, Date/Time, Yes/No

First Field value from the first record in a

number, table, or query

Currency, Date/Time, Yes/No, Text, Memo, OLE object

Last Field value from the last record in a

number, table, or query

Currency, Date/Time, Yes/No, Text, Memo, OLE object

Make no mistake, however. Totals queries are among the most difficult database objects to design

correctly. Virtually anything you might read or hear about Access totals queries (including this

book) uses fairly simple examples to explain the principles behind totals queries. Because of the

infinite variety of data in business applications, it’s impossible to show complex real-world exam-

ples that are relevant to a significant portion of an audience. The good news is that users often

require results that demand nothing more than summing or counting a few columns. Most often,

you can achieve the desired results without too much difficulty. But, now and then, you encounter

an issue that requires more than a simple response.

Unfortunately, no one rule of thumb always leads to a great query design. Very often, you end up

with a compromise solution where some of the calculations are performed within a query, and

more complicated work is performed using VBA code at the form or report level.

Performing totals on all records

You can use total queries to perform calculations against all records in a table or query. For exam-

ple, you can find the total number of products in tblProducts, the average sale price, and the

maximum and minimum retail price for each product category:

1. Add tblProducts and click the Totals button on the to open the Total: row in the QBE
   grid.


2. Add ProductID and SalePrice to the QBE grid.


3. Add SalePrice two more times to the QBE grid.


4. Select Count in the ProductID Total cell.