Counting the Number of Records in a Group

Totals queries are good for grouping records by specific field entries and returning calculations based on just those records. For instance, you can use a Totals query to return the total sales for each salesperson.

First, create your basic query based on your sales tables. Drag the appropriate name or identification and value fields to the query design grid. Choose View, Totals, and select Group By from the name or identification field's Total cell drop-down list. Choose Sum from the value field's Total cell drop-down list. Then, run the query.

The resulting query should display one record for each salesperson and a total of all that person's sales.

Now, let's suppose you want to count the number of sales per salesperson. In that case, you'd follow these steps. First, create your basic query based on your sales tables. Drag the appropriate name or identification fields to the query design grid twice. Next, choose View, Totals. Select Group By from the first name or identification column's Total cell drop-down list. Select Count from the first name or identification column's Total cell drop-down list. Then, run the query.

This time, the query will display one row for each salesperson and the total number of sales for each.

Access Index

Main Index