Crosstab Query is a type of select query that allows you to precise data to make it easier to read and understand. The cross tab query display data in a datasheet. One is down the side of the data sheet and other across the top. You can specify which fields are needs to be in row headings, which should be in the column headings, and which field will contains the values to summarize.
You can use the cross tab wizard to implement aggregate functions such as sum, average, max, min, count, standard deviation, and variance etc. There are mainly three ways to create a crosstab query, they are:
- Using Query Wizard
- Using Query Design
- Using SQL statement
Below here are the complete steps on how to create a crosstab query using the Crosstab Query Wizard.
While creating a crosstab query, you can base it on a table or another query.
In this tutorial we will base it on another query. The query will return the list of artist, genres and albums from the database. The genre is linked to the album –not the artist. So, an artist can release different album from different genres.
Go to the Create tab in the ribbon and click Query wizard option here. This will open the query wizard.
Now make tap to the Crosstab Query Wizardfrom the list and click ok.
Select the table or query that contains the field for the crosstab query results. In this example, select the Query Click Next.
Select the fields that you want to be row headings.
You can select up to three fields. After selecting the fields click to the next option. In order to expand the object’s height, drag or click on their bottom edge.
Make selection for the fields to be displayed in column headings. At last click Next option.
Now select for the field that you want calculated for each row and column intersection.
In the shown example, we have used AlbumName field and the Count()function. As this, will display the number of albums that the artist has released for particular genre. At last click to the Next option.
Assign name for the query. Along with that make selection for whether or not to display the results immediately or to go into design view, so that you can customize the query further.
After then click to Finish option to create the crosstab query.
If you have selected View the query, your query will now run and the result will be displayed.
After creating crosstab query, it will display result like this:
Notice in this result’s screenshot the data is grouped by two fields; row heading and column heading.
In this artist listed as row heading and genre listed as column heading. This allows you to display the summarized data within the results.
This will show the number of albums each artist has released under each genre. So you can see that some artist have released album under different genres.
