Using Aggregate Values in Calculated Fields

Once you add aggregate expressions to your query, you can use those aggregate values in new calculated fields. Use the Expression Builder to build the new calculated field. When you add a calculated field to a query that uses totals, you must specify the total type as expression.

To use aggregate values in calculated fields:

1. Click in the first empty cell in the field row of the query grid.
2. Click the Expression Builder button.
3. Notice that the first folder in the left box is opened by default. The middle box lists all of the aggregate values that the query calculates. The total names indicate what type of calculation is being performed. Double-click a total name to add it to the expression box.
4. Build the expression by adding totals or other fields. When the expression is complete, click OK.
5. Change the field name to something meaningful.
6. Next, be sure to change the total type to expression.
7. When you run the query, you'll see the new field calculation for each record group.

When you display the totals row, every field in the query must have a totals designation. But what if you want to specify criteria for a field, but you don't want to use the field for groupings? Select Where from the total list. Notice that the Show check box is automatically unchecked. The query will use the criteria to specify records, but will not use the field for groupings.

If you need a refresher on using the Expression Builder, see the tasks Adding a Calculated Field to a Query and Using the Expression Builder.

You can also open the Expression Builder by right-clicking in the query grid and selecting Build... from the shortcut menu.