Enter criteria to affect when calculations are performed

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.