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
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
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.
-
Launch the Query Wizard
Click Query Wizard from the Create tab in the Ribbon.
This launches the Query Wizard.
-
Select the Crosstab Query Wizard
Select the Crosstab Query Wizard from the list and click OK.
-
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 >.
-
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 >.
-
Select the Column Heading
Select the field that you want to be the column heading.
Click Next >.
-
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 >.
-
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.
-
The Result
If you selected View the query your query will now run, and the result will be displayed.