Teach MeShow MeLet Me TryPrint

Using Aggregate Functions to Calculate Totals in Queries

Access includes a group of powerful built-in commands known as aggregate(or total) functions. Using aggregate functions, you can easily calculate totals for groups of records returned by the query specifications. Aggregate functions can calculate the sum, minimum, maximum, average, count, variance, and standard deviation. In the query design window, you should include a field to group by as well as the field that contains the values needed for the calculation. The values of aggregate calculations are not stored in the table—instead, Access recalculates the totals each time the query is run.

To use aggregate functions in queries:

  1. Begin with a query that includes the records for which you want to calculate a total. Open the query in Design View.
  2. If necessary, add criteria to the appropriate field or fields.
  3. Click the Totals button to add the total row to the query grid.
  4. If you want the calculation to be performed for groups of records, click in the total row for the field you want to group by. Click the arrow and select Group By. Group By is the default selection for the total cell, so this step may not be necessary.
  5. Next, click in the total cell for the field that contains the values you want to total. Click the arrow and select the aggregate function you want.
  6. Click the Run button.
  7. Notice that the query does not return individual records. Instead, there is one row for each unique value in the Group by field. Each row shows the calculated total for that group of records.



Queries with aggregate calculations are often used to form the basis for strategic analysis or statistical reports. Try creating a query that includes a SUM, MIN, MAX and AVG calculation for the same field. A report based on this query not only shows the total, but more importantly, the range of values that add up to that total.
If you don't include a group, Access will calculate the totals for all the records returned by the query.
You can also display the totals rows by selecting Totals from the View menu.