Section 9.2. Creating Crosstab Queries | Access 2007[c] The Missing Manual

9.2. Creating Crosstab Queries

Access gives you two ways to create a crosstab query: You can use the Crosstab Query wizard, or you can build it by hand. Most Access fans prefer to use the Crosstab Query wizard to get started, and then further refine their query in Design view to add other details, like filtering.

In the following sections, you'll take a crack at cooking up a crosstab query both ways.

Figure 9-3. Consider yourself warned : Don't group using related fields in a crosstab query. In this example, rows are grouped by product name , and columns are grouped by product category. The problem is that every product is in a single category, so each row has data in just one columnthe row for that product's category. To solve this problem and to create a better summary, you could use three levels of grouping, as shown in Figure 9-2.

WORD TO THE WISE
Create a Join Query for Better Grouping

When you use any type of query that involves grouping, you often need to pull information from several different tables. For example, if you're considering a table with sales records, you probably want to pull in additional details about the products sold, the customers who bought them, the location where they were purchased, and so on. Most of the time, this involves bringing together information from several related tables.

The easiest way to prepare for a summary query (like a crosstab query) is to create another query that has all the information you need. This new query will use joins (Section 6.3) to combine all the tables that have the information you need. You can then use this query to build your summary. This technique is particularly useful with crosstab queries, because the Crosstab Query wizard is able to use only one table or query. It can't join tables together on its own.

The AdventureWorks database uses a query named OrderedItems, which forms the basis for all the crosstab queries that you've seen so far. The OrderedItems query gets all the individual items that have been purchased in every order that's ever been made (from the SalesOrder- Details table), and then uses joins to get additional information from the SalesOrderHeader table (which represents the entire order), the Customers table, the Products table, the Store table, and the ShipMethod table. You need to make several jumps to get to the customer address information, which lets you profile how sales stack up in different cities, states, and countries . (You can study this query by downloading the AdventureWorks database from the "Missing CD" page at www.missingmanuals.com.)

9.2.1. Creating a Crosstab Query with the Wizard

The easiest way to build a crosstab query is with the Crosstab Query wizard. If you want to try it out yourself, follow these steps using the AdventureWorks database:

If you want to bring together information from linked tables, start by creating a join query .

In this example, you'll use the already created OrderedItems join query that draws on a wealth of information about the ordered items, the corresponding products, the customers, the geographic location where they live, and so on. For more information about building a join query of your own, see Section 6.3.

If you decide that you can get everything you need from a single table, you can skip this step.
Choose Create Other Query Wizard .

Here's where the wizard magic begins. A New Query window appears, with a list of the different types of queries the wizard can create.
Choose Crosstab Query Wizard, and then click OK .

The first step of the wizard prompts you to pick a table or query (Figure 9-4). Make your selection by clicking one of the buttons in the View box.

Figure 9-4. To see a list of tables in your database, click Tables. To see the queries, click Queries.
Select your table. If you want to choose a query, click the Queries option, and then choose your query from the list. Click Next .

In this example, you need to pick the Queries option, and then choose the OrderedItems query.

The next step asks you to supply the grouping criteria that will be used to combine your data into rows (Figure 9-5).

If you're creating a simple two-level crosstab query, you pick one criterion for rows and one for columns (in the next step). However, it's possible to pick up to three levels of grouping for rows. This approach works best if the different levels are related. For example, you can choose to group rows by customer country, subgroup each country by city, and subgroup each city by customer ID. See Figure 9-2 for an example of a nicely subgrouped crosstab query.

Figure 9-5. To use a field as a row heading, select it in the Available Fields list, and then click the funny > button to move it to the Selected Fields list.
Add the fields you want to use to the Selected Fields list, and then click Next .

In the OrderedItems example, rows are grouped by the StateProvince field. You can easily change your grouping in the query design window after you try out your query. For example, if you wanted, you could switch the StateProvince field to the Country field. You'll learn how to manipulate a crosstab query in Design view in Section 9.2.2.

The next step asks you to supply the grouping criteria that's used to split your rows into columns (Figure 9-6). This time, you can choose only one field.

Choose the field you want to use for column grouping, and then click Next .

In this example, it's the ProductCategory field.

The last step asks you to pick what calculation you want to perform to create your summary.

Figure 9-6. As you move through the wizard, Access shows a mini-preview of the structure of your in-progress crosstab query at the bottom of the window. In this example, rows are grouped by StateProvince and columns by ProductCategory.

Choose the field you want to use for your calculation, and then choose a summary function (Figure 9-7) .

For example, you can choose to find the lowest -priced sale, the order with the highest number of units sold, the average item price, and so on. In this example, you're using the OrderQty field to count the number of items sold.

Figure 9-7. In this example, the Sum function totals up the OrderQty value from each record. For example, this query tells you that you've sold a total of 53 items from the Bike category to customers in Alabama. If you want to count how many orders your customers have made (instead of how many items you've shipped), you'd need a slightly different queryin this case, you'd use the Count function to count the number of distinct SalesOrderID values.

UP TO SPEED
Picking the Right Groups

Trying to decide which field to use for row grouping and which one to use for column grouping? If these two fields are unrelated (and they should be), it makes sense to use the field that creates the fewest groups for column grouping. That's because tables with lots of rows and few columns are easier to read (and print) than tables with lots of columns and few rows.

For example, if you're grouping by product name and country, it's a safe bet that you'll wind up with more product groups than country groups. (For example, you may have customers in eight different countries but a product catalog with 480 items.) So use the product grouping for rows, and the country grouping for columns.

If you want to show a subtotal for each row, turn on the "Yes, include row sums" checkbox .

The row subtotal is shown in the very first column. For example, if you activate this option with the states and categories query, the total sales for each state are shown in the first column, followed by a category-by-category breakdown (Figure 9-8).

Figure 9-8. The final crosstab query highlights the relationship between states and the types of products their inhabitants buy. (At the far left is the total for each state or province across all product categories.) Who knew Californians prefer bikes to clothing?

Click Next .

The final step asks you to supply the query name. You can then choose to run the query and view its results, or continue editing it in Design view. If you need to apply filtering, head over to Design view. Otherwise, it's time to see the fruit of your labor.
Click Finish .

9.2.2. Creating a Crosstab Query from Scratch

As with any query, you can fine-tune a crosstab query in Design view. You can also create a new crosstab query from scratch by following these steps:

Choose Create Other Query Design .

Access creates a new, blank query, and opens it in Design view.
Add the table or query you want to use from the Show Table dialog box, and then click Close .

If you're using the AdventureWorks database, the easiest option is to choose the Queries tab of the Show Table box, and then add the OrderedItems query.

Tip: Alternatively, you can close the Show Table dialog box and just drag the tables you want from the navigation pane onto the query design surface.

Choose Query Tools Design Query Type Crosstab .

Access converts your query into a crosstab query. Crosstab queries look like totals queries, with one difference. In the field list at the bottom of the window, you find an extra property named Crosstab (Figure 9-9).

Figure 9-9. Like a totals query, crosstab queries include the Total property where you specify whether a field is used for grouping, filtering, or a summary calculation. Unlike a totals query, crosstab queries also include a Crosstab property where you specify the crosstab placement of the fieldin a row, in a column, as a value, or not shown at all (in which case you're probably using the field for sorting or filtering).

Choose the fields you want to use in your crosstab query .

Every field in a crosstab query plays one of the following roles:
- It's used for row grouping . In this case, set the Total property to Group By and the Crosstab property to Row Heading.
  
  Although the Crosstab wizard limits you to three fields for row grouping, you can actually add a virtually unlimited number of fields for column grouping. Make sure you arrange them in the order you want them applied. For example, if you have two row grouping fields, the field on the left is used first for grouping, and then the groups are subdivided using the next field.
- It's used for column grouping . In this case, set the Total property to Group By and the Crosstab property to Column Heading.
  
  You must use exactly one field for this purpose. Remember, column grouping is applied after your row grouping.
- It's displayed as a value in the table . In this case, set the Total property to the summary function you want to use (like Sum, Count, Avg, and so on), and then set the Crosstab property to Value.
  
  You must use exactly one field for this purpose. However, you can use an expression that performs a calculation based on more than one field. For example, the crosstab queries shown in Figures 9-1 and 9-2 use the expression Revenue: [UnitPrice]*[OrderQty] to calculate the total revenue for each line item in an order.
  
  Tip: You may remember that the Crosstab Query wizard gives you the option of showing the total for each row in a separate column. Figure 9-10 shows how to create the same effect on your own.
- It's used for filtering . In this case, set the Total property to Where, and set the Crosstab property to "(not shown)." Then, fill in your filter criteria in the Criteria slot. (See Section 6.2.1.1 for a review of filter expressions.)
  
  Note: Unfortunately, you can't use filtering or sorting on the calculated field. That means that if you're creating a query that totals sales numbers , you can't filter out just the rows with high sales totals. However, you can perform the same feat with a pivot table, as described in the next section.
  
  Figure 9-10 shows the query definition for the query you built with the wizard in the previous section (Figure 9-8).