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:
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 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.