Using a Crosstab Query to Present Summary Data

In this post we shall use the power of an Access Crosstab Query to summarize and restructure our data into a clear and concise format.  We shall illustrate how to do this with sales made by employees of a Real Estate Company (who are referred to as Estate Agents in the UK).

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.

As you can see in Figure 1 above, this raw data provides us with all the necessary information, but is difficult to digest in its present form.  What we are interested in, is the total SalesValue, for each Employee, for each Quarter.  With the help of a Crosstab Query, we can restructure this information so that each Employee Name becomes a single Row Heading on the left side of the table, and each Quarter Value is grouped together to form Column Heading's across the top of the table.  Since there are three employees, and four quarters in our data, this will give us a table with three rows and four columns.  The Sum of the Sales value then appears where each Employee Row intersects with each Quarter Column.  This is how our Crosstab Query will be structured:

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

1. Select the CREATE TAB of the Access Ribbon.
2. Click the QUERY DESIGN icon.  It is located in the OTHER group.
3. Select the table or query to be used from the SHOW TABLE dialogue box.  The one I have used is called qrySales.
4. 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.
5. Drag the three field names from qrySales down onto the grid.
6. 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.
7. Next go to the Crosstab row of the Quarter column.  Then select Column Heading from the drop down list.
8. 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. 

The QUERY DESIGN GRID should  now look like this:

Figure 2: The QUERY DESIGN GRID for our Crosstab Query.

 When you run the Crosstab Query our results should appear like this:

Figure 3: Results of our Crosstab Sales Query.

Its worth pointing out that we can add another row heading containing a Total SalesValue for each SalesPerson across the four quarters.  To do this just go back to the grid and add an additional column for SalesValue (you might want to give the column the alias of Total).   Then select Row Heading from the Crosstab Row on the grid. When run, it should look like this:

Figure 4: Crosstab Query with additional Row Heading comprised of the row Total.