Refining Queries  «Prev  Next»
Lesson 11 Working with Crosstab queries
Objective Explain what Crosstab queries are and how to create them.

Working with Crosstab Queries

Crosstab queries, although somewhat confusing, are one of the more powerful query types that display information. Crosstab queries give you a cross-tabulation of your information. Taking the example in the last lesson further, what if you want to display the data not only for each of the projects, but for months as well? The results set of this task would look like this:

Results set of this task
results set of this task

To create the crosstab you want, once again start out creating a Select query, displaying the fields you want. In this case, the fields are ProjectDescription; a calculated expression,
Month: Format(DateWorked,”mmm”); and the HoursWorked field, which will be summed up.
Once you have created the Select query, you can click on the Crosstab query choice from the Query Type toolbar button. When you do this, you will see the Total row added above a new row called the Crosstab row. In the Crosstab row, you will have three choices: Row Heading, Column Heading, or Value.
Take a look at the following MouseOver to see how each of the options just mentioned is used in a Crosstab query.

you can pick aggregate functions to perform
  1. As with Totals queries, you can pick aggregate functions to perform. For the row heading and column heading, this is set to Group By
  2. In this case, ProjectDescription, this is the value that will be displayed for each row
  3. This option specifies the column heading across the page. In this case, it is Months
  4. This is the value that will be displayed for each column and row

Crosstab Query Details
You may notice that the data in the Month columns are not in order of month. To control the order of columns specifically, use the Column Header property of the query.
In the next lesson, we will conclude this module.