Using Aggregate Functions to Summarize Numeric Data

By using aggregate functions, you can easily summarize numeric data. You can use aggregate functions to calculate the sum, average, count, minimum, maximum, and other types of summary calculations for the data in a query result. These functions let you calculate one value for all the records in a query result or group the calculations as desired. For example, you could determine the total sales for every record in the query result, as shown in Figure 15.26, or you could output the total sales by country and city, as shown in Figure 15.27. You could also calculate the total, average, minimum, and maximum sales amounts for all customers in the United States. The possibilities are endless.

Figure 15.26. Total sales for every record in a query result.

graphics/15fig26.jpg

Figure 15.27. Total sales by country and city.

graphics/15fig27.jpg

To create an aggregate function, follow these steps:

Add to the query grid the fields or expressions you want to summarize. It's important that you add the fields in the order in which you want them grouped. For example, Figure 15.28 shows a query grouped by country and then by city.

Figure 15.28. Selecting from a drop-down list the type of calculation for the Total row.
Click Totals on the toolbar or select View | Totals to add a Total row to the query. By default, each field in the query has Group By in the Total row.
Click the Total row on the design grid.
Open the combo box and choose the calculation you want, as shown in Figure 15.28.
Leave Group By in the Total row for any field you want to group by, as shown in Figure 15.28. Remember to place the fields in the order in which you want them grouped. For example, if you want the records grouped by country and then by contact title, you must place the Country field to the left of the ContactTitle field on the query grid. On the other hand, if you want records grouped by contact title and then by country, you must place the ContactTitle field to the left of the Country field on the query grid.
Add to the query the criteria you want.

Figure 15.29 shows the design of a query that finds the total, minimum, maximum, and average sales by country and city; Figure 15.30 shows the results of running the query. As you can see, aggregate functions can give you valuable information.

Figure 15.29. A query that finds the total, minimum, maximum, and average sales by country and city.

graphics/15fig29.jpg

Figure 15.30. The result of running a query that has many aggregate functions.

graphics/15fig30.jpg

If you save this query and reopen it, you should see that Access has made some changes to its design. Access changes the Total cell for Sum to Expression, and it changes the Field cell to the following:

TotalSales: Sum([UnitPrice]*[Quantity])

If you look at the Total cell for Avg, you should see that Access changes it to Expression. Access changes the Field cell to the following:

AverageSales: Avg([UnitPrice]*[Quantity])

Access modifies the query in this way when it determines that you're using an aggregate function on an expression that has more than one field. You can enter the expression either way. Access stores and resolves the expression as noted.

Task: Building a Query to Summarize and Total Data

Modify the query used in the last task to show the total sales by country, city, and order date. Before you continue, save the query as qryCustomerOrderInfo and then close it. On the Query tab of the Database window, click qryCustomerOrderInfo. Choose Copy | Paste from the toolbar. Access should prompt you for the name of the new query. Type qryCustomerOrderSummary and click OK. With qryCustomerOrderSummary selected, click the Design command button. Delete both the UnitPrice and Quantity fields from the query output. To turn the query into an aggregate function, follow these steps:

Click Totals on the toolbar. Notice that Access adds an extra line, called the Total row, to the query grid; this line says Group By for all fields.
Group by country, city, and order date, but total by the total price (the calculated field). Click the Total row for the TotalPrice field and use the drop-down list to select Sum. (Refer to Figure 15.28.)
Run the query. Access should group and sort the result by country, city, and order date, with a total for each unique combination of the three fields.
Return to the query's design and remove the order date from the query grid.
Rerun the query. Notice that now you're summarizing the query by country and city.
Change the Total row to Avg. Now you're seeing the average price multiplied by quantity for each combination of country and city. Change the row to Sum, and save the query.

As you can see, aggregate functions are both powerful and flexible. You cannot edit their output, but you can use them to view the sum, minimum, maximum, average, and count of the total price, all at the same time. You can easily modify whether you're viewing this information by country, country and city, and so on, all at the click of a mouse.