databasedev.co.uk - database solutions and downloads for microsoft access

Creating Crosstab Queries in Microsoft Access:

A Microsoft Access crosstab query presents summary information in a compact format that is similar to a spreadsheet. These types of queries can present a large amount of summary data in a format that is usually simpler to analyse than viewing the information in a database form. Each attribute (field) in a table typically contains a category of data. A crosstab query summarizes the data from one or more of these fields that are separated into groups based on one or more fields.

When to use a Microsoft Access Crosstab Query

For example, if you wanted to analyse the number of hours per project per month, it might be difficult to use a select query because you would have to scroll through the records of the many employees who had worked on various projects, and then try to make a comparison between the projects. A crosstab query would reduce the number of records presented by adding up the total hours per individual project.

In the crosstab query, which is a special type of Totals query, the Total row that appears in the query design grid will always be active. You can not toggle the Total row off when using a crosstab query.

If we look at the following examples, that show both a table (containing information on Project Times) and the crosstab query view, you will see how the crosstab query presents the summary information based upon that table:

The Project Time table, that will be used as the source for the crosstab query.

The crosstab query, presenting the summary information that is based upon the previous table.

Note how the crosstab query has summarized the data from 72 records contained in the table into 6 records in the query. This now presents the Total Hours worked on each Project and gives a further breakdown of how many Hours have been worked each Project per Month. This provides a much easier way to view the information.

Examining the design of the Crosstab Query

If we view the design of the previous crosstab query we will see that it uses information from the ProjectTime table and it summarizes the Hours worked per Project per Month. It displays the information as one record per Project.

The Crosstab row, visible in the example below, is inserted between the queries Total row and Sort row in the design grid. This designates the Row and Column Headings and Values that will be summarized in the crosstab query.

The Group By clause is specified in both the Total cells of the Row and Column Headings and an aggregate Total cell (Sum, in this case) is set for the Value field.

The design of the crosstab query, showing the Crosstab row and the Group By and Aggregate settings.

The Microsoft Access Crosstab Query Wizard

A crosstab query can be produced by you, however, it is usually quicker to make use of the Crosstab Query Wizard available in Microsoft Access. The Crosstab Query Wizard has some limitations, but you can work around most of them by having the Wizard create the query and then modifying it to suit your needs.

Which ever way you decide to go, you must specify the following three items:

• Row Heading field: The name of the field selected as the row heading begins the first column in the query results datasheet. Each value in this field is displayed in the first column as a row title.
• Column Heading field: The values in the field selected as the column heading are displayed as headings for the remaining columns in the query results datasheet.
• Value field: The values in the selected field are summarized by the values in the rows and columns. You must designate the summary operation to be performed. For example, you might want to sum or average the values in a field.

In a crosstab query, you can specify only one Value field and one Column Heading field, but you can have multiple Row Heading fields. For example, you might want to know who at what company ordered how many of what product. In that case, you could designate Company and Contact as Row Headings, Product as Column Heading, and Quantity as Value.

Crosstab Query Limitations

• To use multiple tables for the crosstab query, you will first need to create a separate query that has the tables required in it. The Crosstab Query Wizard will only allow you to select one table or one query for the row and column headings.
  
  To work around this limitation: you will need to create a query that includes all of the fields from the multiple tables and save this as an intermediate query. You can then use this as the record source for the crosstab query wizard.
• You can not specify limiting criteria whilst using the crosstab query wizard.
  
  To work around this limitation: Create the crosstab query using the wizard and then go into the query design to apply the criteria.
• Column Headings or Column Orders can not be specified whilst using the wizard
  
  To work around this limitation: Again, create the crosstab query using the wizard and then go into the query design and modify it.

To run the Crosstab Query Wizard, click on the New button in the Query Objects section of the database window and select the Crosstab Query Wizard from the New Query dialog box - you can then follow the wizard prompts:

• The source for the query (either a table or query)
• The field(s) to be used as the Row Heading(s)
• The field to be used for the Column Heading
• The field to be used as the Value field
• The Title for the query.

After selecting these options, Microsoft Access will create the Crosstab Query and run it for you.