Access 2010: The Missing Manual

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 by 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 column—the 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 ...