Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

670

FIGURE 18.33

A typical crosstab query datasheet

Crosstab queries are extremely powerful. The preceding example is exceedingly simple, but with a

bit of additional work, valuable information can be obtained from the tables underlying the query.

For instance, this crosstab could be extended by adding criteria that restrict records to a certain

sales date interval or salesperson. You could use a query to combine certain information, such as

sales by category and state, and then use it as input into a crosstab that calculates average sales by

category by date.

The Crosstab Query Wizard has some limitations, however:

l (^) To use more than one table for the crosstab query, you need to create a separate query
that has the tables you need for the crosstab query. For example, you may have a Group
By row heading from tblCustomers and a Group By column heading from tblProd-
ucts (Category). The Crosstab Query Wizard allows you to select only one table or query
for the row and column heading.
l You can’t specify the limiting criteria for the crosstab when working with the Crosstab
Wizard. Use the wizard to build the crosstab, and then go in and set the query’s criteria as
a separate step.
l (^) You cannot use a calculated field for row or column headings. In this case, add the calcu-
lated field to an intermediate query and use the query for the wizard.
l (^) Column headings or column orders cannot be specified. Again, have the wizard create the
query and then modify it.