So in what way does a Crosstab Query improve the presentation of summary data? Well lets imagine our Real Estate Company asks us to summarize a list of sales made by each of its employees, for each quarter, over the period of a year. This list has fields for Employee, Quarter and SaleValue.
|Figure 1: This is the raw data that will form |
the basis of our Crosstab Query.
||Quarter 1|||Quarter 2|||Quarter 3|||Quarter 4||
|Employee 1|||Sum of SaleVale|||Sum of SaleVale|||Sum of SaleVale|||Sum of SaleVale||
|Employee 2|||Sum of SaleVale|||Sum of SaleVale|||Sum of SaleVale|||Sum of SaleVale||
|Employee 3|||Sum of SaleVale|||Sum of SaleVale|||Sum of SaleVale|||Sum of SaleVale||
Creating a Crosstab Query
So how do we create a Crosstab Query?
Well, there are two main ways: the first with the Crosstab Query Wizard, and the second using the Query Design Grid. We are going to focus on the second method which involves creating the Crosstab Query from Scratch. However if you do wish to use the Crosstab Query Wizard, you can select the CREATE TAB, click the QUERY WIZARD ICON from the OTHER group, highlight CROSSTAB QUERY from the list in the dialogue box, and then click OK. When the Wizard starts, follow the instructions to select the Table/Query, the field to be used as Row Headings (SalesPerson), the field to be used as Column Headings (Quarter), and the aggregate function to be used to summarize the SaleValue (Sum).
However, here is the method to create a Crosstab Query from Scratch using the Query Design Grid:
- Select the CREATE TAB of the Access Ribbon.
- Click the QUERY DESIGN icon. It is located in the OTHER group.
- Select the table or query to be used from the SHOW TABLE dialogue box. The one I have used is called qrySales.
- Click the CROSSTAB icon. This is located in the QUERY TYPE group of the DESIGN ribbon. Notice how two new rows, Crosstab and Totals, appear in the query design grid.
- Drag the three field names from qrySales down onto the grid.
- Go to the Crosstab row of the SalesPerson column on the QUERY DESIGN GRID. Then select Row Heading from the drop down box in that cell.
- Next go to the Crosstab row of the Quarter column. Then select Column Heading from the drop down list.
- As you may recall, the sum of SaleValue is going to provide the summary data in our table. To do this, go to the Totals row of the SaleValue column. Then select Sum from the drop down list. Then move down to the Crosstab row of the SaleValue column, and then select Value from the drop down list.
|Figure 2: The QUERY DESIGN GRID for our Crosstab Query.|
|Figure 3: Results of our Crosstab Sales Query.|
|Figure 4: Crosstab Query with additional Row Heading comprised of the row Total.|