O'Reilly logo

Access 2010: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

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.

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required