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.
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:
TotalSales: ([UnitPrice]*[Quantity])- ([UnitPrice]*[Quantity]*[Discount])
(You don't have to enter the brackets; Access will add those for you.)
Figure 9-32. Specify Total options for the EmployeeID and TotalSales fields.
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:
The other three options are listed here:
note
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.
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:
TotalSales: ([UnitPrice]*[Quantity])- ([UnitPrice]*[Quantity]*[Discount])
Figure 9-36. This query contains only one field.
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