MS Access —

Summarizing Groups of Records

When you work with queries, you will often be less interested in the individual records and more interested in summarized information about groups of records. A query can calculate information about a group of records in one or more tables.

For example, you could create a query that finds the total amount of tea your company sold to China in 1998 or how much all that tea cost. The Total row lets you group and summarize information in a query. The Total row normally is tucked away from view in the query design windowyou can make the Total appear by clicking the Totals button on the toolbar or by selecting View » Totals from the menu. Once the Total row is displayed, you can tell Access how you want to summarize the fields.

Using a query to find the total number of tickets and sales, grouped by the TourName field and the Number of Tickets field.

  1. Click the qryTourSales query and then click the Design button.

    First you need to add the field that you want to group data by onto the design grid. You want to calculate the total sales and number of tickets sold for each tour package, so you will group the query by the TourName field.

  2. Double-click the TourName field in the tblTours field list.

    The TourName field appears as the first field in the design grid. Next you need to add the fields you want to summarize.

  3. Double-click the Number of Tickets and Cost fields in the tblCustomerTours field list.

    To summarize your query, you must summon the Total row. To summon the Total row, click the Totals button on the toolbar or select View » Totals from the menu.

  4. Click the Totals button on the toolbar.

    Another way to display the Total row is to select View » Totals from the menu.

    The Total row appears in the design grid. "Group By" must remain in the TourName Total row to group the records by the TourName field. Next you need to select the fields you want to summarize and the calculation you want to perform on them.

  5. Click the Total row in the Number of Tickets column and click the list arrow that appears.

    A list of calculations appears, similar to those shown in figure. All you have to do is simply select the calculation you want to perform on the field. table describes the available calculations.

  6. Select Sum from the list.

    This will total the values in the Number of Tickets field.

  7. Click the Total row in the Cost column, click the list arrow, and select Sum from the list.

    You can specify criteria to limit the records you want to be calculatedsimply enter the criteria in the Criteria row of any grouped or calculated fields. If the field you want to use for the criteria isn't one of the grouped or calculated fields, you must use the "Where" option in the field's Total row. The "Where" option limits the records used in the calculation without being included in the query results.

    You want to calculate only those records from the second quarter of the year.

  8. Double-click the Date field in the tblCustomerTours field list.

    Here's how to add criteria to the Date field.

  9. Click the Total row in the Date column, click the list arrow, and select Where from the list.

    The "Where" option is used only to limit recordsits results cannot be displayed in the results of the query. Access automatically unchecks the "Show" check box.

  10. Click the Date column's Criteria row and type Between 4/1/00 and 6/30/00.

    You're ready to see the results of the new query.

  11. Click the Run button on the toolbar.

    Access displays the results of the query, which calculates the total sales and number of tickets sold for each tour package.

  12. Save the query as qryTourTotals and then close the query.

Total Options

Group By

Groups the values in the field so that you can perform calculations on the groups.

Sum

Calculates the total (sum) of values in a field.

Avg

Calculates the average of values in a field.

Min

Finds the lowest value in a field.

Max

Finds the highest value in a field.

Count

Counts the number of entries in a field, not including blank (Null) records.

StDev

Calculates the standard deviation of values in a field.

Var

Calculates the variance of values in a field.

First

Finds the values from the first record in a field.

Last

Finds the values from the last record in a field.

Expression

Tells Access that you want to create your own expression to calculate a field.

Where

Specifies criteria for a field to limit the records included in a calculation.

To calculate or summarize a group of records:

  1. display the query in design view.

  2. if necessary, click the totals button on the toolbar.

  3. move the field that you want to group data by onto the design grid. make sure group by appears in that field's total row.

    note: the field(s) you want to group by must appear first in the design grid and have group by in their total row.

  4. move the field that you want to perform calculations on onto the design grid.

  5. choose the type of calculation that you want to perform by clicking the field's total row, clicking the list arrow, and selecting a calculation from the list.