Let's continue using the Northwind database to get some extra practice with crosstab queries.
Assume that the employee who is responsible for taking the order also decides on the shipper. You're Northwind's president, and you're reviewing how the company uses its three shippers. You're curious to see whether different levels of management have a preference for one shipper over another. You'll use a crosstab query to help.
The Crosstab Query
What data do you need? The Employees table has job titles, whereas the Shipper table has shipper names and the Orders table has OrderIDs. If you count the OrderIDs, you will get the breakdown of orders by job title for each shipper.
Create a new query in Design view and add the Employees, Orders, and Shippers tables.
Save the query as Shippers By Employee.
Choose Query, Crosstab Query. The Total and Crosstab rows are displayed.
From the Employees field list, double-click Title to add it to the design grid.
In the Total row, keep Group By. In the Crosstab row, choose Row Heading.
In the Shippers table, double-click CompanyName to add it to the grid. In the Total row, keep the Group By setting. In the Crosstab row, choose Column Heading.
From the Orders field list, double-click OrderID to add it to the grid. If you count the OrderIDs by each shipper and by job title, that gives you the information you're looking for.
In the Total row, select Count. In the Crosstab row, select Value (see Figure 9.21).
Figure 9.21. The crosstab query has a row heading, a column heading, and a value.
Click View. Access provides the breakdown of orders by shippers.
Refine Your Crosstab Query
It would be nice to have a column of total orders for each employee classification.
Click View to return to Design view.
Double-click OrderID from the Orders field list to add it to the grid.
Set the Total row to Count and the Crosstab setting to Row Heading.
In the Field row of the column, click directly before the O in OrderID and type Total Orders:.
Click View. You now have the total orders for each employee classification. After reviewing the data further, you'd also like to see the breakdown by employee for each title.
Click View to return to Design view. From the Employees field list, select LastName and drop it into the CompanyName column.
LastName is now the second column on the grid.
Keep the Group By selection in the Total row and select Row Heading for the Crosstab entry.
Click View to see your records. You now have the breakdown of employees, along with their title. (You can view the query in the solution database NorthwindEndChap9.mdb.)
Close the query and save your changes.