Pin Me

Crosstab Queries in Access 2007

written by: •edited by: Tricia Goss•updated: 6/3/2010

In Microsoft Access 2007, crosstab queries that resemble traditional table-like structures can be created to help present data in ways that make it much easier to analyze. In this guide, we’ll show how to create these objects using the Crosstab Query Wizard.

  • slide 1 of 1

    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.)

    Select the Query Wizard 

    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.