How to use the Crosstab Query Wizard in Access

Create a crosstab query within minutes using the Query Wizard.

A crosstab query is a special type of query that calculates a sum, average, or other aggregate function, and then groups the results by two sets of values — one down the left side of the datasheet and the other across the top.

You specify which fields are to be row headings, which field contains column headings, and which field contains the values to summarize.

Below, I'll demonstrate how to use the Crosstab Query Wizard to create a crosstab query.

  • The Result

    Screenshot of crosstab query results.

    Once we've created our crosstab query, it will display results like in this screenshot.

    Notice that the data is grouped by two fields; row headings and column headings.

    We've got each artist listed as row headings, and we've got each genre listed as column headings.

    This allows us to display the summarized data within the results.

    This example shows the number of albums each artist has released under each genre. So we can see that some artists have released albums under different genres. The query also shows the total number of albums.

    This is just sample data. In the real world, those artists have released a lot more albums than this.

  • The Data

    Screenshot of query results.

    When you create a crosstab query, you can base it on a table, or another query.

    In this example, we'll base it on another query. The query returns this list of artists, albums, and genres from the database.

    The genre is linked to the album — not the artist. So, an artist can release different albums from different genres.

  1. Screenshot of the Query Wizard button highlighted.

    Launch the Query Wizard

    Click Query Wizard from the Create tab in the Ribbon.

    This launches the Query Wizard.

  2. Screenshot of the Query Wizard.

    Select the Crosstab Query Wizard

    Select the Crosstab Query Wizard from the list and click OK.

  3. Screenshot of the Crosstab Query Wizard.

    Select the Table or Query

    Select the table or query that contains the fields for the crosstab query results.

    In this example, we select a query (which returns data from three tables).

    Click Next >.

  4. Screenshot of the Crosstab Query Wizard.

    Select the Row Heading/s

    Select the field/s that you want to be row headings.

    You can select up to three fields. However, in this example, we only select one.

    Click Next >.

  5. Screenshot of the Crosstab Query Wizard.

    Select the Column Heading

    Select the field that you want to be the column heading.

    Click Next >.

  6. Screenshot of the Crosstab Query Wizard.

    Select the Data Field and Function

    Select the field that you want calculated for each row and column intersection.

    In this example, we use the AlbumName field and the Count()function. This will display the number of albums that the artist has released for a particular genre.

    We also leave Yes, include row sums checked, so that a sum of all genres is displayed for each artist.

    Click Next >.

  7. Screenshot of the Crosstab Query Wizard.

    Name your Query and Finish

    Provide a name for the query (or leave it at the name that Access suggests).

    Also choose whether or not to display the results immediately, or to go into Design View, so that you can customize the query further.

    Click Finish to create the crosstab query.

  8. Screenshot of the Crosstab Query result.

    The Result

    If you selected View the query your query will now run, and the result will be displayed.