Making Calculations on Multiple Records

One of QBE's most powerful capabilities is that of obtaining summary information almost instantly from specified sets of records in tables. Summarized information from databases is the basis for virtually all management information systems (MIS). Such systems usually answer questions such as, "What are our sales to date for this month?" or "How did last month's sales compare with the same month last year?" To answer these questions, you must create queries that make calculations on field values from all or selected sets of records in a table. To make calculations on table values, you must create a query that uses the table and employs Access's SQL aggregate functions to perform the calculations.

Using the SQL Aggregate Functions

Summary calculations on fields of tables included in query result tables use the SQL aggregate functions listed in Table 10.1. These are called aggregate functions because they apply to groups (aggregations) of data cells. The SQL aggregate functions satisfy the requirements of most queries needed for business applications. You can write special user-defined functions with Access VBA code to apply more sophisticated statistical, scientific, or engineering aggregate functions to your data.

StDev() and Var() evaluate population samples. You can choose these functions from the drop-down list in the Query Design grid's Total row. (The Total row appears when you click the Totals button of the toolbar or choose View, Totals.) StDevP() and VarP() evaluate populations and must be entered as expressions. If you're familiar with statistical principles, you recognize the difference in the calculation methods of standard deviation and variance for populations and samples of populations. The following section explains the method of choosing the SQL aggregate function for the column of a query.

Making Calculations Based on All Records of a Table

Managers, especially sales and marketing managers, are most often concerned with information about orders received and shipments made during specific periods of time. Financial managers are interested in calculated values, such as the total amount of unpaid invoices and the average number of days between the invoice and payment dates. Occasionally, you might want to make calculations on all records of a table, such as finding the historical average value of all invoices issued by a firm. Usually, however, you apply criteria to the query in order to select specific records that you want to total.

Access considers all SQL aggregate functions to be members of the Totals class of functions. You create queries that return any or all SQL aggregate functions by clicking the Totals button (with the Greek sigma Σ, which represents summation) on the toolbar.

The Orders table of Access 97's Northwind.mdb sample database does not include an OrderAmount field that represents the total amount of the order, less freight. (The "Entering a Query Criterion" section of Chapter 9 used a simplified version of this example to demonstrate the use of functions to calculate field values.) To create a sample query that uses the SQL aggregate functions to display the total number of orders, total sales, and the average, minimum, and maximum order values, you need a field that contains the total amount of each order. Follow these steps to create a new table that includes an additional field with a computed Order Amount:

1.	Create a new query and add the Orders and Order Details tables to it.
2.	Drag the OrderID field of the Orders table to the first column of the Query Design grid, and then drag the OrderDate field to the second column.
3.	Type Order Amount: Sum([Quantity]*[UnitPrice]*(1-[Discount])) in the Field row of the third (empty) column. This expression sums the net amount of all line items for each order. With the caret in the Order Amount column, click the Properties button of the toolbar to open the Field Properties window. Type Currency in the text box for the Format property to format your new column.
4.	Click the Totals button on the toolbar. A new row, Total, is added to the Query Design grid. Access adds Group By, the default action, to each cell in the Totals row. The following section discusses the use of Group By.
5.	Move to the third column's Total row and press F4 to display the drop-down list of SQL aggregate functions. Select Expression from the list. Your Query Design grid appears as shown in Figure 10.41. Figure 10.41. Creating a calculated field with the Sum() function.
6.	Click the Run button of the toolbar to test your initial entries. Your query in Datasheet view appears as in Figure 10.42. Figure 10.42. Running the query design shown in Figure 10.41.
7.	Close and save your query with the name Order Totals. Note When you apply the Format property to the Order Amount column by selecting or typing Currency in the Field Properties window, successive queries that you create do not inherit the value of the Format property (instead, the default Format value, Double, is applied). If you type Order Amount: CCur(Sum([Quantity]*[Unit Price]*(1-[Discount]))) in the Order Amount column's Field row, however, the Format property of successive queries containing the Order Amount field will be set to Currency. The CCur() function coerces the field's data type to Currency.

Follow these steps to apply the SQL aggregate functions to the Order Amounts field of the query result set of the Order Totals query:

1.	Open a new query and add the Order Totals query. (To base a query on a previously saved query, click the Queries tab of the Show Table dialog, and add the query as you would a table.)
2.	Drag the OrderID field to the first column and then drag the Order Amount column four times to the adjacent column to create four Order Amount columns.)
3.	Choose View, Totals to add the Totals row to your Query Design grid. Alternatively, right-click in the grid region and choose Totals from the pop- up menu.
4.	Move to the Total row of the OrderID column and press F4 to display the drop-down list of SQL aggregate functions. Choose Count as the function for the Order ID, as shown in Figure 10.43.
5.	Move to the first Order Amount column, open the list, and choose Sum from the Total drop-down list. Repeat the process, choosing Avg for the second Order Amount column, Min for the third, and Max for the fourth.
6.	Place the caret in the Count field, and click the Properties button of the toolbar (or right-click in the Count field and then click Properties in the pop-up menu) to display the Field Properties window. Type Count as the value of the Caption property.
7.	Repeat step 6 for the four Order Amount columns, typing Currency for the Format property and typing Sum, Average, Minimum, and Maximum as the values of the Caption property for the four columns, respectively. (You don't need to set the Format property if you used the CCur() function in the Order Totals query.)
8.	Click the Run button of the toolbar to display the query's result. You haven't specified criteria for the fields, so the result shown in Figure 10.44 is for the whole table. (The values in the result may differ from the values in Figure 10.44 because of records that Microsoft might add to the sample database after this book is published.) Figure 10.43. Choosing the SQL aggregate function for calculations based on multiple records in a table. Figure 10.44. The result of the all-records query shown in Figure 10.43.
9.	Save your query with a descriptive name, such as qrySQLAggregates, because you use this query in the two sections that follow. Note When you run qrySQLAggregates, the Jet database engine determines that Order Totals is a query (QueryDef object) rather than a table (TableDef object). Thus Jet executes the Order Totals query before executing the qrySQLAggregates query. One of the most important features of Access is that you can execute queries against the query result sets (Recordset objects) of other queries—a process called nesting queries. In theory, at least, there is no limit to the depth to which you can nest Access queries. As you increase the number of queries in the chain, however, execution slows for the last query in the nested sequence.

Making Calculations Based on Selected Records of a Table

The preceding example query performed calculations on all orders received by North- wind Traders that were entered in the Orders table. Usually, you are interested in a specific set of records—a range of dates, for example—from which to calculate aggregate values. To restrict the calculation to orders that Northwind Traders received in March 1995, follow these steps:

1.	Click the Query View button on the toolbar to return to design mode so that you can add criteria to select a specific group of records based on the date of the order.
2.	Drag the OrderDate field onto the OrderID column to add OrderDate as the first column of the query. You need the OrderDate field to restrict the data to a range of dates.
3.	Open the Total drop-down list in the Order Date column, and choose Where to replace the default Group By. Access deselects the Show box of the OrderDate column. (If you attempt to show a column that provides the SQL WHERE restriction, you receive an error message when you run your query.)
4.	In the Order Date column's Criteria row, type Like "3/*/95" to restrict the totals to orders received in the month of March 1995 (see Figure 10.45). When you use the Like criterion, Access adds the quotation marks if you forget to type them. Figure 10.45. Adding a Where criterion to restrict the totals to a range of records.
5.	Choose View, SQL to display your query's SQL statement. The Where criterion in the Total row adds a WHERE clause to the SQL statement— in this case, WHERE ((([Order Totals.OrderDate Like "3/*/94")))—to restrict the totaled records to the records for the specified date range. (The Access query parser tends to add extra sets of parentheses to expressions.) If you don't add the Where instruction to the Total row, the query result consists of rows with the totals of orders for each day of March 1995, not for the entire month.
6.	Click the Run button on the toolbar to display the result: the count, total, and average value of orders received during the month of March 1995 (see Figure 10.46). Figure 10.46. The result of adding the Where criterion to the query.

You can create a more useful grouping of records by replacing the field name with an expression. For example, you can group aggregates by the year and month (or year and quarter) by grouping on the value of an expression created with the Format() function. The following steps produce a sales summary record for each month of 1995, the latest year for which 12 months of data are available in the Orders table:

1.	Click the Design View toolbar button, and then click the header bar of the query's OrderDate column to select the first column. Press the Insert key to add a new, empty column to the query.
2.	Type Month: Format( [OrderDate],"yy-mm") in the first (empty) column's Field row. (You use the "yy-mm" format so that the records sort in date order. For a single year, you also can use "m" or "mm", but not "mmm" because "mmm" sorts in alphabetic sequence starting with Apr.)
3.	Change the Where criterion of the Order Date column to Like "*/*/95". Your query design appears as shown in Figure 10.47.
4.	Click the toolbar's Run button to display the result of your query (see Figure 10.48). The query creates sales summary data for each month of 1995. Figure 10.47. Designing a query for a yearly sales summary by month. Figure 10.48. The result set of the query design shown in Figure 10.47.
5.	Choose View, SQL to display the SQL statement that created the query result set. The SQL statement in the SQL window of Figure 10.49 has been reformatted for clarity. (Formatting a SQL statement with spaces and newline pairs does not affect the statement's execution.)
6.	Choose File, Save As and save the query under a different name, such as qryMonthlySales, because you modify the query in the next section.