- Create a select query in Design view and add the tables whose records you want to use in the calculation.
- Add the fields on which you want to perform calculations and specify criteria.
- Click Totals
on the toolbar.
Microsoft Access displays the Total row in the design grid.
- For each field in the design grid, click its cell in the Total row, and then click one of the following aggregate functions: Sum, Avg, Min, Max, Count, StDev, or Var, or click the other functions, First and Last.
- If you want, enter criteria to affect the results of the calculation.
In the query design grid, where you specify criteria determines when the calculation is performed and can produce different query results.
Note Although the following examples only show queries that calculate totals, these guidelines apply to calculated fields as well.
Limit groups before performing calculations on groups of records- Specify the criteria in the Group By fields as the following example shows.
This query totals extended prices for ...
... companies in Canada and the UK only.
Return selected results after calculations are performed- Specify criteria in the field that contains the calculation as the following example shows.
This query totals extended prices for companies in Canada and the UK ... ... but shows only those that are less than $10,000.
Limit records before they are grouped and before the calculation is performed- Add to the design grid the field whose records you want to limit, and then specify criteria in the field's Criteria cell.
If you're calculating totals in the same query, set the Total cell for the field containing the criteria to Where. This rule applies whether you're calculating the total on all records or groups of records. (Microsoft Access automatically clears the Show check box.)
The following example uses the ExtendedPrice field twice, once to limit the records and once to calculate the total. However, you can use a different field to limit records by dragging that field to the design grid and setting its Total cell to Where.
From these records, the query retrieves only those with extended prices greater than $500.00 before it groups or totals ... ... and then it sums and shows only those totals for companies in Canada or the UK.
This total for Seven Seas Imports does not include the order for $210.00.
- Specify the criteria in the Group By fields as the following example shows.
- If you want, sort the results.
- Click View
on the toolbar to view the results.
Calculate totals on groups of records in a query
- Create a select query in Design view and add the tables whose records you want to use in the calculation.
- Add the fields on which you want to perform calculations, define groupings, and specify criteria.
- Click Totals
on the toolbar.
Microsoft Access displays the Total row in the design grid.
- For the field or fields you want to group on, leave Group By in the Total cell.
- 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, or click the other functions, First and Last.
- If you want, enter criteria to affect the results of the calculation.
In the query design grid, where you specify criteria determines when the calculation is performed and can produce different query results.
Note Although the following examples only show queries that calculate totals, these guidelines apply to calculated fields as well.
Limit groups before performing calculations on groups of records- Specify the criteria in the Group By fields as the following example shows.
This query totals extended prices for ... ... companies in Canada and the UK only.
Return selected results after calculations are performed- Specify criteria in the field that contains the calculation as the following example shows.
This query totals extended prices for companies in Canada and the UK ... ... but shows only those that are less than $10,000.
Limit records before they are grouped and before the calculation is performed- Add to the design grid the field whose records you want to limit, and then specify criteria in the field's Criteria cell.
If you're calculating totals in the same query, set the Total cell for the field containing the criteria to Where. This rule applies whether you're calculating the total on all records or groups of records. (Microsoft Access automatically clears the Show check box.)
The following example uses the ExtendedPrice field twice, once to limit the records and once to calculate the total. However, you can use a different field to limit records by dragging that field to the design grid and setting its Total cell to Where.
From these records, the query retrieves only those with extended prices greater than $500.00 before it groups or totals ... ... and then it sums and shows only those totals for companies in Canada or the UK. This total for Seven Seas Imports does not include the order for $210.00.
- Specify the criteria in the Group By fields as the following example shows.
- If you want, sort the results.
- Click View
on the toolbar to view the results.
Note If you add a calculated field that includes one or more aggregate functions to a query in which you're calculating totals on all the records, you must set the calculated field's Total cell to Expression.