Grouping and Summarizing Records

3 4

Groups are typically considered a report function, but you can group records in a query, usually for the purpose of summarizing data. A group is a collection of related records and as such can be beneficial when you're analyzing data. You saw how easy it is to create a group by sorting (in a manner of speaking) in the previous section. However, grouping is usually just a by-product of a sort, not the goal of a sort. Nor is the result considered a group.

Computing Totals for Grouped Records

When you group a set of records, you can also perform calculations based on each group. For example, to create a query that returns the total sales for each employee in the Northwind database, follow these steps:

1. Create a new query in Design view.
2. Add the Orders and Order Details tables to the query.
3. Add the EmployeeID from the Orders table to the design grid.
4. Enter the following expression in the Field cell of the second column:

    TotalSales: ([UnitPrice]*[Quantity])- ([UnitPrice]*[Quantity]*[Discount]) 

   (You don't have to enter the brackets; Access will add those for you.)

5. Choose View, Totals. A Total row appears in the design grid.
6. In the EmployeeID field's Total cell, make sure the Group By option is selected.
7. In the TotalSales field's Total cell, select Sum, as shown in Figure 9-32.

   

   Figure 9-32. Specify Total options for the EmployeeID and TotalSales fields.

8. Run the query to see the results shown in Figure 9-33.

   

   Figure 9-33. The query groups records for each employee and computes the total sales for that employee.

As you can see, the query returns just one record for each entry in the EmployeeID field. In other words, the query returns one record for each employee or salesperson. The Sum aggregate function in the TotalSales field sums the values for each group (determined by the Group By aggregate in the Total cell), returning a total sales amount for each employee.

Take a look at the Jet SQL statement for this query, shown here:

 SELECT Orders.EmployeeID,  Sum(([UnitPrice]*[Quantity])-([UnitPrice]*[Quantity]*[Discount]))  AS TotalSales FROM Orders  INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.EmployeeID; 

The TotalSales field displays the results of the expression we entered. The INNER JOIN relates the OrderID field in the Orders and Order Details tables. The GROUP BY clause indicates that the results are to be grouped by the EmployeeID.

You can choose from a number of options and aggregate functions after you display the Total row. As you saw in the previous example, Access defaults to the Group By option. An aggregate function returns information about a set of records, and nine of these twelve options are aggregate functions, as follows:

• Sum. Totals the values for each group.
• Avg. Averages the values for each group.
• Min. Returns the lowest value in each group.
• Max. Returns the highest value in each group.
• Count. Returns the number of items in each group (excluding Nulls and blanks).
• StDev. Returns the standard deviation for each group.
• Var. Returns the variance for each group.
• First. Returns the first value in the group.
• Last. Returns the last value in the group.

The other three options are listed here:

• Group By. Defines the group by reducing the data to unique entries.
• Expression. Returns a calculation based on an aggregate function.
• Where. Specifies search criteria.

note

---

Access sorts the results of a totals query even if you don't specify a sort order. Since Access must group the underlying records, this behavior might seem obvious, but it's easy to miss if you don't realize it's happening. (You don't notice it in Figure 9-33 because Access is sorting by the EmployeeID value [the primary key], which you can't see in the query's results.) Sorting will be apparent only when the query explicitly names a sort order.

Suppose you want to count the number of items in each group. Simply return the query to Design view, select Order Details.OrderID in the Field cell of the third column, and then select Count for the Total field, as shown in Figure 9-34.

Figure 9-34. This query includes a field to count the number of items in each group.

The results of this query are shown in Figure 9-35.

Figure 9-35. The CountOfOrderID field contains the number of items in each group.

The CountOfOrderID field includes the number of items in each group, which equates to the number of different items sold by each employee (not the quantity of items or the number of orders).

The Jet SQL statement for this updated query is shown here:

 SELECT Orders.EmployeeID,  Sum(([UnitPrice]*[Quantity])-([UnitPrice]*[Quantity]*[Discount]))  AS TotalSales, Count([Order Details].OrderID) AS CountOfOrderID FROM Orders INNER JOIN [Order Details]  ON Orders.OrderID = [Order Details].OrderID GROUP BY Orders.EmployeeID; 

Notice the Count function, as follows:

 Count([Order Details].OrderID) AS CountOfOrderID 

This expression counts the number of OrderID entries in each EmployeeID group.

Troubleshooting - My query returns a Cannot group on fields selected with " * " error

Occasionally, a query that groups records will return the error Cannot group on fields selected with "*" ? If you didn't use the asterisk (*) to specify all the fields, which would be the most obvious problem, the query's Output All Fields property is probably set to Yes. Because the Group By option creates groups based on specified fields, you must be careful to add only those fields you want to group or evaluate with a Totals option. When the query's Output All Fields property is set to Yes, every field in the data source, not just the ones you added to the design grid, is a group. Consequently, the query results aren't grouped at all. The solution is simple: In query Design view,

right-click the query's background, and then select Properties from the shortcut menu to display the Query properties sheet. If the Output All Fields property is set to Yes, simply change it to No.

Computing Grand Totals

In the previous example, we summed total sales by employee (groups). But group totals aren't the only summarizing you can do.

Follow these steps to calculate a grand sales total:

1. Create a new query in Design view.
2. Add the Order Details table to the query.
3. Add the following expression to the design grid:

    TotalSales: ([UnitPrice]*[Quantity])- ([UnitPrice]*[Quantity]*[Discount]) 

4. Choose Views, Totals, and then, in the TotalSales field's Total field, select Sum, as shown in Figure 9-36.

   

   Figure 9-36. This query contains only one field.

5. Run the query to see the results shown in Figure 9-37.

   

   Figure 9-37. This time, the TotalSales field returns just one value: a sales grand total.

This query has a fairly simple Jet SQL statement, shown here:

 SELECT Sum(([UnitPrice]*[Quantity])-  ([UnitPrice]*[Quantity]*[Discount]))  AS TotalSales FROM [Order Details]; 

The query displays just one entry in one field, the results of the following expression:

 Sum(([UnitPrice]*[Quantity])-([UnitPrice]*[Quantity]*[Discount])) 

The query calculates the extended price for every item and then totals those results for a grand total.

note

---

Don't be alarmed if Access rearranges the design grid when you run a query, especially if your query contains an aggregate function. Access is just stating your question in the most efficient manner.