Performing Calculations by Using a Query

You typically use a query to locate all the records that meet some criteria. But sometimes you are not as interested in the details of all the records as you are in summarizing them in some way. For example, you might want to know how many orders have been placed this year or the total dollar value of all orders placed. The easiest way to get this information is by creating a query that groups the necessary fields and does the math for you. To do this, you use aggregate functions in the query.

Access queries support the aggregate functions shown in the following table.

Function	Calculates
Sum	Total of the values in a field
Avg	Average of the values in a field
Count	Number of values in a field, not counting Null (blank) values
Min	Lowest value in a field
Max	Highest value in a field
StDev	Standard deviation of the values in a field
Var	Variance of the values in a field

In this exercise, you will create a query that calculates the total number of products in an inventory, the average price of all the products, and the total value of the inventory.

USE the 07_Calculate database. This practice file is located in the Chapter06 subfolder under SBS_Access2007.

OPEN the 07_Calculate database.

1.	On the Create tab, in the Other group, click the Query Design button. Access opens the query window and the Show Table dialog box.
2.	In the Show Table dialog box, double-click Products, and then click Close. Access adds the Products table to the query window and closes the Show Table dialog box.
3.	In the Products Items field list, double-click ProductID and then UnitPrice. Access copies both fields to the design grid.
4.	On the Design contextual tab, in the Show/Hide group, click the Totals button. Access adds a row named Total to the design grid.
5.	In the ProductID column, click the Total arrow, and then in the list, click Count. Access enters the word Count in the Total cell. When you run the query, this function will return a count of the number of records containing a value in the ProductID field.
6.	In the UnitPrice column, click the Total arrow, and then in the list, click Avg. When you run the query, this function will return the average of all the UnitPrice values.
7.	In the Results group, click the Run button. The query returns a single record containing the count and the average price.
8.	Switch back to Design view.
9.	In the third column, in the Field cell, type UnitPrice*UnitsInStock, and press . Access changes the expression you typed to Expr1: [UnitPrice]*[UnitsInStock] This expression will multiply the price of each product by the number of units in stock.
10.	Select Expr1 and type Value of Inventory to re-label the expression.
11.	In the third column, click the Total arrow, and then in the list, click Sum. Access will return the sum of all the values calculated by the expression.
12.	On the Design tab, in the Results group, click the Run button.

CLOSE the query window without saving your changes, and then close the 07_Calculate database. If you are not continuing directly on to the next chapter, quit Access.