Crosstab Queries

Take almost any subject: sports, finance, politics. The professionals who make their money analyzing what goes on are always talking about "breaking down the numbers." Traffic accidents in Pleasantville totaled 72 last month; how many occurred east of Main Street, and how many west of Main Street? The freshman class at Ivy University has 1,784 students; how many come from each country? Total employees at Acme Widget number 3,587; how many are in each of the Production, Marketing, and Financial divisions?

Often you'll want to find and compare breakdowns among different items in a single group. For example, you might want to see the country-by-country totals for the freshman class at each of the Ivy League schools.

This is the kind of information delivered by the crosstab query, a type of totals query that summarizes data by tabulating it across the values of two fields (thus the term crosstab, short for "cross tabulation"). As with other totals queries, row headings are actual values. Unlike other totals queries, however, column headings in a crosstab query are also values rather than field names and totals. You'll soon see how this arrangement provides a matrix that generates totals where the two values intersect.

You can create a crosstab query from scratch or by using the Crosstab Query Wizard in the New Query dialog box. But the wizard has its downside: You can use only one table. In any case, as with the Find Unmatched Query Wizard, it pays to understand how the wizard works so you can work with it and modify it when necessary. When you create a crosstab query from scratch, the wizard becomes self-explanatory.

TIP

You can use a workaround for the one-table limitation of the Crosstab Query Wizard. You can create a multitable query and then use the query as the basis for the wizard. But in that case, it might be just as easy to create a crosstab query from scratch.

Overview

Let's briefly examine the steps required for creating a crosstab query before you actually make one.

You start a crosstab query like any other: You create a new query in Design view and add the tables that have the data you need to the Design window. When you choose Query, Crosstab Query, you'll see two new rows in the design grid: Total and Crosstab.

A crosstab query has three key elements:

• Row headings You add one or more fields to the grid to use as row headings. In other words, these columns form the values down the "side" of your crosstab result. The Total row should be set to Group By; the Crosstab row is set to Row Heading.

• Column headings The values Access finds in this field form separate columns across the top of your crosstab result. You add only one field to the grid for these column headings. Set the Crosstab row to Column Heading and the Total row to Group By.

• Value This is the calculation displayed in the intersection of the Row Heading values and the Column Heading values. You add the field whose values will be used in your calculation. You set the Crosstab row to Value, and the Total row to the type of summary you want.

You can also add criteria. First add the field that will further define the records retrieved. Set the Total row to Where and keep the Crosstab row empty. Type your expression in the Criteria row.

Creating a Crosstab Query

Suppose you're a Nifty Lions owner. You want to see how many orders each of your shippers is handling, broken down by state. You can use a crosstab query to view this information.

What data do you need? The Orders table has OrderIDs. The Shippers table has the names of shippers. And the Customers table has a field for the customer's state. If you count OrderIDs, you can find the number of orders each shipper handled by state.

1.	Create a new query in Design view.
2.	Add the tblCustomers, tblOrders, and tblShippers tables. Note that the tables are tied together by one-to-many relationships.
3.	Save the query as qryOrdersByShipper.
4.	Choose Query, Crosstab Query.
5.	From the tblCustomers table, double-click CustState to add it to the grid. In the Total row, keep Group By. Click in the Crosstab row, open the drop-down list, and choose Row Heading.
6.	From the tblShippers table, double-click ShipCompanyName to add it to the grid. In the Total row, keep Group By. Open the drop-down list in the Crosstab row and select Column Heading.
7.	From the Orders table, double-click OrderID. Click in the Total row, highlight Group By, and type c for "count." Click in the Crosstab row, open the drop-down list, and select Value (see Figure 9.14). Figure 9.14. In a crosstab query, you specify both column and row headings and a value that will be summed.
8.	Click View to run the query and see the breakdown of orders by shipper by state.

Additional Row Headings

You can add more row headings to break down the values further. For example, suppose that for each state you'd like to see a further breakdown between regular and overnight orders. Here's how to add the necessary column:

1.	Click View to return to Design view.
2.	In the Orders field list, click Overnight. Drag and drop it into the ShipCompanyName column. The Overnight column is the second column from the left.
3.	In the Crosstab row of the Overnight column, type r for "row heading."
4.	Click View to see the breakdown. You'll notice that orders for some states, such as California and Colorado, are now divided between regular and overnight orders.

Add a Total Column

You might want to add a column that shows the total orders for each state. Let's name this column Total Orders. Here are the steps:

1.	Click View to return to Design view.
2.	Double-click OrderID in the Orders field list to add it to the grid.
3.	Set the Total row for this column to Count and the Crosstab row to Row Heading.
4.	In the Field row, click directly before the O in OrderID and type Total Orders:.
5.	Click View to see the orders for each row.

Add Criteria

You can also add criteria to count only certain records. Here are some examples:

Include Only Orders Before a Certain Date

Note that the total orders for Iowa is 5 and for Pennsylvania is 3.

1.	Click View to return to Design view.
2.	From the Orders field list, double-click the ShippedDate field to add it to grid.
3.	Set the Total row to Where.
4.	In the Criteria row, type <8/25/04 to include only orders before August 25.
5.	Click View. Note that the state totals are now 4 for Iowa and 1 for Pennsylvania.

Include Only Orders for Certain States

You can also limit orders to those from certain states.

1.	Click View to return to Design view.
2.	Cut the Criteria text for the ShippedDate field (you'll paste it a little later).
3.	Double-click the CustState field in the Customers field list.
4.	Open the drop-down list in the Total row for the new field and set it to Where.
5.	In the Criteria row, type Like c* to display only states beginning with c.
6.	Click View. Only records for states beginning with the letter c are displayed. Note that California's nonovernight orders total 3.

Include Only Orders for Certain States Before a Certain Date

Now you'll include criteria for both the state and date.

1.	Click in the Criteria row of the ShippedDate field.
2.	Paste the text you just cut: <8/25/04 (see Figure 9.15). Figure 9.15. In this crosstab query, the record set is restricted by both the state and the date.
3.	Click View to see your records (see Figure 9.16). Figure 9.16. Unlike a select query, a crosstab query has values in both column and row headings.
4.	Because you now exclude orders on or after 8/25/04, California's nonovernight records total only 2.
5.	Save the changes and close the query. Close the Nifty Lions database.