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
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.
1)
Total Row 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)
Row heading In this case, ProjectDescription, this is the value that will be displayed for each row.
3)
Column heading This option specifies the column heading across the page. In this case, it isMonths.
4)
Value This is the value that will be displayed for each column and row.
Crosstab Query Details
Details of a Crosstab query
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.
Controlling the Column Headings in Access
Most of the time, you want to have the columns in a set order for display. In some cases, the natural order of the column heading will be good enough. Sometimes you want to have the order be different than one derived by using the Column Header option. To do this, you can set the Column Headings property, found on the property sheet in the top of the Query Design view. When you click the Property Sheet toolbar button, you will see the property sheet for the query.
You can put the column headings in as you want, separated by commas, as shown here:
Column headings separated by commas
Another benefit of using column headings is that those columns that have a null value will still be displayed, as seen here:
Columns that have a null value displayed
Note that you have to have the column headings in the Column Heading property of the query match the Crosstab row entry for Column Heading.
