Making Calculations on Multiple Records

One of SQL's most powerful capabilities is 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) and business intelligence (BI) projects. These systems or projects usually answer questions: 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 employ Jet'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 11.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.

Table 11.1. SQL Aggregate Functions

Function	Description	Field Types
Avg()	Average of values in a field	All types except Text, Memo, and OLE Object
Count()	Number of Not Null in a field	All field typesvalues
First()	Value of a field ofsthe first record	All field type
Last()	Value of a field ofthe last record	All field types
Max()	Greatest value in field	All numeric data types and Text
Min()	Least value in field	All numeric data types and Text
StDev(), StDevP()	Statistical standard deviation of values in a field	All numeric data types
Sum()	Total of values in a field	All numeric data types
Var(), VarP()	Statistical variation of values in a field	All numeric data types

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 on 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.

T-SQL

ANSI SQL and most SQL (client/server) databases support the equivalent of Access SQL's Avg(), Count(), First(), Last(), Max(), Min(), and Sum() aggregate functions as AVG(), COUNT(), FIRST(), LAST(), MAX(), MIN(), and SUM(), respectively. T-SQL also provides equivalents of Jet's StdDev(), StdDevP(), Var(), and VarP() functions with the same names.

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 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, s, which represents summation) on the toolbar.

Follow these steps to apply the five most commonly used SQL aggregate functions to the sample Order Subtotals query:

1. Open a new query and add the Order Subtotals query.

2. Drag the OrderID column to the first new query column and then drag the Subtotal column four times to the adjacent column to create four Subtotal columns.

3. Click the Totals button to add the Totals row.

4. Move to the Total row of the OrderID column and press Alt+ to display the drop-down list of SQL aggregate functions. Choose Count as the function for the OrderID column, as shown in Figure 11.49.

   Figure 11.49. You apply the Count() function to one of the rows of the query that has a value in every row to obtain the total number of rows returned by the query. The OrderID column is the logical choice for counting.

   

5. Move to the first Subtotal column, open the list, and choose Sum from the Total drop-down list. Repeat the process, choosing Avg for the second Subtotal column, Min for the third, and Max for the fourth.

6. Place the cursor in the OrderID field and click the Properties button on 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 Subtotal columns, 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, because the Subtotal column is formatted as Currency.)

8. Click Run to display the query's result. The query design doesn't have fields suitable for row-restriction criteria, so the result shown in Figure 11.50 is for the whole table.

   Figure 11.50. The datasheet displays the five SQL aggregate values for all records of the Orders table.

   

9. Save your query with a descriptive name, such as qryOrdersAggregates, because you use this query in the two sections that follow.

Making Calculations Based on Selected Sets of Rows or Records

The preceding sample query performed calculations on all orders received by Northwind 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 received in March 1998, follow these steps:

1. Return to Query Design view and add the Orders table to the qryOrdersAggregates query. Access automatically creates the join on the OrderID fields. If you didn't create this query, you can import it from the Join11.mdb sample database.

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 OrderDate column and choose Where to replace the default Group By. Access deselects the Show box of the OrderDate column.

4. In the OrderDate column's Criteria row, type Like "3/*/1998" to restrict the totals to orders received in the month of March 1998 (see Figure 11.51). When you use the Like criterion, Access adds the quotation marks if you forget to type them.

   Figure 11.51. The OrderDate field of the Orders table is needed to restrict the aggregate data to orders received within a specified period, March 1998 in this case.

   

   Tip

   The examples of this chapter use Jet Like "{m|*}/{d|*}/yyyy" expressions as shorthand for Between #mm/dd/yyyy# And #mm/dd/yyyy# WHERE clause date constraints. Current versions of the Microsoft Data Access Components and the Jet OLE DB driver don't recognize Like "*/*/yyyy" and similar Like expressions. If you plan to copy Jet SQL statements to Visual Basic 6.0 or Visual Basic .NET programs, use the Between...And operator, not Like for dates.

5. Click the Run button on the toolbar to display the result for orders received during the month of March 1998 (see Figure 11.52).

   Figure 11.52. This datasheet shows the effect of adding a date criterion, in this case, orders received in March 1998.

   

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 1997, the most recent year for which 12 months of data are available in the Orders table:

1. Return to Query Design view, 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],"yyyy-mm") in the first (empty) column's Field row. (You use the "yyyy-mm" format so that the records group 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 OrderDate column to Like "*/*/1997" to return a full year of data. Your query design appears as shown in Figure 11.53.

   Figure 11.53. This query design returns a row containing aggregate values of orders received in each month of 1997.

   

4. Click Run to display the result of your query (see Figure 11.54). The query creates sales summary data for each month of 1997.

   Figure 11.54. The datasheet displays aggregate rows for each month of 1997.

   

5. Choose File, Save As and save the query under a different name, such as qryMonthlyOrders1997, because you modify the query in the next section.