Calculate a sum, average, count, or other total on groups of records in a query

1. Create a select query in Design view. Add the tables whose records you want to use in the calculation, and then add the fields on which you want to perform calculations, define groupings, and specify criteria.
   
   
2. Click Totals on the toolbar.

   Microsoft Access displays the Total row in the design grid.

3. For the field or fields you want to group on, leave Group By in the Total cell.
   
   
4. For each field you want to calculate, click its cell in the Total row, and then click one of the following aggregate functions: Sum, Avg, Min, Max, Count, StDev, or Var.

   

   For information on each aggregate function, click . (For special considerations when using the First and Last functions, click

5. If you want, enter criteria to affect the results of the calculation.

   How?

6. If you want, sort the results.

   How?

7. Click View on the toolbar to view the results.

If you didn't get the results you expected, click .

Notes

• When you use an aggregate function on a field, Microsoft Access combines the function and the field names to name the field in the datasheet (for example, AvgOfFreight). For information on renaming these fields to better indicate their contents, click .
  
  
• If you add a calculated field that includes one or more aggregate functions in its expression, you must set the calculated field's Total cell to Expression.
  
  
• To create a crosstab query that groups totals down the left and across the top of the datasheet, click .
  
  
• The Category Sales for 1995 query in the Northwind sample database has an example of calculating a sum for groups of records. To view this query, open the Northwind database in your Office folder's Samples subfolder, and then open the Category Sales for 1995 query in Design view. For information on opening Northwind, click .