Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

666

FIGURE 18.26

A datasheet created by an Expression total

Creating Crosstab Queries

Access supports a specialized type of total query — the crosstab — that summarizes data in a row-

and-column format.

Understanding the crosstab query

A crosstab query is a spreadsheet-like summary of the things specified by the row and column head-

ers created from your tables. In this specialized type of total query, the Total row in the QBE pane

is always active and cannot be toggled off in a crosstab query.

In addition, the Total row of the QBE pane specifies a Group By option for both the row and the col-

umn headings. Like other total queries, the Group By option specifies the row headings for the query

datasheet and are based on the contents of the field. However, unlike other total queries, the crosstab

query also obtains its column headings from values in a field rather than from table field names.

Note
The fields used as rows and columns must always have Group By in the Total row. Otherwise, Access reports
an error when you attempt to display or run the query.

The datasheet itself contains a calculation (count, sum, average, and so on) in every cell (intersec-

tion of a row and a column). For instance, when basing a crosstab query on categories and prod-

ucts, the cells in the datasheet may contain a sum of all sales by category, by product.

For example, you may want to create a query that displays the average retail price of each product

make, by category. For instance, say you want to see the average price of Ford cars, trucks, and

SUVs. You want the make (Ford, for example) as the row heading, and the product category (car,

truck, and so on) as the column heading, with each cell containing an average retail price for each

make by category. Figure 18.27 shows the completed query (we build this query in the following

discussion).