| | Product Information Help and How-to Training Templates Support and Feedback Technical Resources Additional Resources | Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.
Total records in a query (MDB)
 Calculate totals on all the records in a query
- Create a select query (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.) 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 (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.).
- Click Totals
 on the toolbar.
Microsoft Access displays the Total row in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE 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.
How?
In the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE 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 (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.) as well.
Limit groups before performing calculations on groups of records
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.
- 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 (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) in Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.) 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 (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.).
- Click Totals
on the toolbar.
Microsoft Access displays the Total row in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE 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.
How?
In the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE 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 (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.) as well.
Limit groups before performing calculations on groups of records
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.
- If you want, sort the results.
- Click View
on the toolbar to view the results.
Note If you add a calculated field (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.) that includes one or more aggregate functions (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.) to a query in which you're calculating totals on all the records, you must set the calculated field's Total cell to Expression.
|
United States
Email this link
Printer-Friendly Version
Share
Hide All
