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.
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.
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.
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.
- Totals button on the toolbar.
Tip: Another way to display the Total row is to select View
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.
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 6-12. All you have to do is simply select the calculation you want to perform on the field. Table 6-5 describes the available calculations.
Select Sum from the list.
This will total the values in the Number of Tickets field.
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.
Double-click the Date field in the tblCustomerTours field list.
Here's how to add criteria to the Date field.
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.
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.
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.
Save the query as qryTourTotals and then close the query.
Table 6-5. Total Options
Groups the values in the field so that you can perform calculations on the groups.
Calculates the total (sum) of values in a field.
Calculates the average of values in a field.
Finds the lowest value in a field.
Finds the highest value in a field.
Counts the number of entries in a field, not including blank (Null) records.
Calculates the standard deviation of values in a field.
Calculates the variance of values in a field.
Finds the values from the first record in a field.
Finds the values from the last record in a field.
Tells Access that you want to create your own expression to calculate a field.
Specifies criteria for a field to limit the records included in a calculation.