Summarizing Data

3 4

You can also use the Simple Query Wizard to summarize data—for example, to compute the total of a field. To do so, you must specify a Number field in the query.

To use the Simple Query Wizard to summarize shipping costs, follow these steps:

1. On the Database window’s Objects bar, click Queries, and then click New on the Database window toolbar.
2. In the New Query dialog box, double-click Simple Query Wizard.
3. In the Tables/Queries drop-down list, select Table: Orders.
4. In the Available Fields list, double-click CustomerID and Freight to add both fields to the Selected Fields list. Click Next.
5. Select the Summary option, and then click the Summary Options button.
6. In the Summary Options dialog box (see Figure 9-7), select the Sum check box and the Count Records In Orders check box. Click OK, and then click Next.

   

   Figure 9-7. Use this dialog box to compute the sum of the Freight field and to count the number of orders in a query.

7. On the final page of the wizard, enter a new name for the query or accept the default, and then click Finish.

The results of the query are shown in Figure 9-8.

Figure 9-8. The query created with the wizard returns the total freight cost and the total number of orders for each customer.

As you can see, this query returns one record for each customer. The Sum Of Freight field displays the total freight cost for each customer; the Count Of Orders field shows the total number of orders for each customer.

The Jet SQL statement for this query is shown here:

 SELECT DISTINCTROW Orders.CustomerID, Sum(Orders.Freight)  AS [Sum Of Freight], Count(*) AS [Count Of Orders] FROM Orders GROUP BY Orders.CustomerID; 

This Jet SQL statement is different from the qryPhoneNumbers query shown earlier in this chapter. This time the SELECT statement includes the DISTINCTROW keyword, which returns a unique recordset (excluding duplicates).

The following expression displays the sum of the entries in the Freight field in a field named Sum Of Freight (shown in the AS clause):

 Sum(Orders.Freight) AS [Sum Of Freight] 

As before, the FROM clause identifies the data source, but this time it’s followed by a GROUP BY clause that groups the records by the entries in the CustomerID field.

Grouping is discussed in more detail in the section "Grouping and Summarizing Records.")

A lot goes on behind the scenes when you use the Simple Query Wizard. Comparing the Jet SQL statement to the Design view is a good way to familiarize yourself with the logic behind a query.