Learn How to Create a Crosstab Query in Microsoft Access 2007

Learn How to Create a Crosstab Query in Microsoft Access 2007
Page content

Crosstab Queries

A crosstab query in Microsoft Access is a special type of query that can be created when you want to describe one numerical quantity in terms of two other fields. For example, suppose you have a table that contains the sales figures of your entire inventory for the whole year, and you want to know how much money was made per product during each month of the year. This would be a perfect opportunity to construct a crosstab query to display the information.

Although the creation of this type of query is similar in nature to the simple queries we discussed in a previous tutorial, there are some notable differences which we’ll describe in the following steps.

Creating a Crosstab Query in Access 2007

Step 1: Open the database that contains the table or query from which you want to construct the crosstab query.

Step 2: Click on the Query Wizard button located within the Create tab on the Access ribbon. (Click any image for a larger view.)

Step 3: Select Crosstab Query Wizard and then click OK to continue.

Select Crosstab Query Wizard

Step 4: In the first window of the Crosstab Query Wizard, select the table or query from which you want to build your crosstab query.

Select Table or Query

Note that if you click on the radio button next to Tables under View, only tables will be shown as options in the list of objects from which to choose. The same is true if you select Queries. If you want all tables and queries to be listed, click the radio button next to Both.

Click Next to continue.

Step 5: Select which fields you would like to use as row headings. You can select up to three fields here as opposed to column headings where you can only make one selection. This is a good thing to keep in mind when you’re trying to decide which fields to represent as rows and which to use as column headers. Click Next to continue.

Choose Category for Row Headings

Step 6: Pick the field that you want to use for column headers. Click Next to continue.

Choose Field for Column Headings

Step 7: Choose the field that you want to use as the basis for the calculated values that will appear in the query from the list of Fields. Once you have chosen a field, a list of valid Functions associated with that field will appear. Select the function that you want to use to analyze your data.

If you want the crosstab query to include summary rows, put a check in the appropriate box in this window. Click Next to continue.

Select Calculated Field

Step 8: Either type in a name or accept the default name assigned by Access for the newly created query. Click the Finish button to continue.

Name the Crosstab Query

The query will now be visible in the main Access viewing window.

Query Viewed in Acces

For more tips and tutorials, browse through Bright Hub’s library of Microsoft Access user guides. Among these items, find articles on how to import Excel spreadsheets into Access, understanding data types, and how to design a form from scratch. Additional guides are added on a regular basis, so keep checking back.