6.6: Crosstab Query

Standard MS Access queries produce results with column headings. Crosstab queries are queries where results are displayed with both row and column headings.

We limit our discussion to the use of the Crosstab Query Wizard for creating crosstab queries.

Example

As an example, suppose we wish to display for each department a count of the number of 3 and 6 credit hour courses. The counts are to appear in matrix format where rows are labeled with department names and are columns appear with labels 3 and 6. Below is an outline of how the results should appear:

Figure 6.22: Query results to appear with row and column headings

Crosstab queries have at least three fields; one field (department) is used for row labels, another field (credit hours) is used for column labels, and one field (course number) is used with an aggregate function (Count).

We can begin by creating a Simple query that retrieves all the necessary values:

Next, we save the query (say Q1) and create a new query – click on the Query Wizard:

The wizard prompts for

• • • type of query – choose Crosstab Query Wizard
    • the table/query to use as the basis for the new crosstab query (Select Queries and then Q1 – the query previously saved)
    • the field to use for row labels  deptName
    • the field to use for column labels  creditHours
    • the field (courseNo) and the aggregate function (Count) to use for summarizing data.

Running the query shows several columns: the department name (values in this column are the row labels), total over the remaining columns for the row, columns for credit hour values 3 and 6 (the column labels). A sample run: