How To Convert Normal Query To Crosstab Query In Access 2016?

Normal query return results, where some fields need to be duplicated in order to show all the data. Whereas crosstab query allows you to group the query result in such a way where there is no duplicate value present and the result is easier to read and decode.

Here's an example of both normal and crosstab query

Normal (Select) Query

Normal Query displays monthly product sales from each city because there are many months; each city needs to be duplicated for each month of sales.

Crosstab Query

In crosstab query the months have been shifted to the top, as column headers. This time, each city will get displayed only once because the months are listed in the columns.

So learn how can you convert the normal query into the crosstab query.

Steps To Convert Normal Query To Crosstab Query In Access


From the navigation pane, make a right click to theQuery and select Design View from the contextual menu. By this way, the query will get open in design view.


Go to the Design Tab in the ribbon and from the Query Type group, click to the Crosstab. This will easily convert up your query from normal query to crosstab query.

Alternatively, you can also make a right click in diagram pane and select Query Type>Crosstab Query from the contextual menu.


Now you can see that a crosstab row has been added to the bottom criteria pane.

Crosstab Row will help in specifying which fields will appear as row headers and which field will appear as row headers.

Whereas Total Row allows you to specify how the results will be grouped as well as any expressions/functions to apply to a given field.

In the shown example, the Ship City field is the row header and the Order Date field is the column header.


If you run the query, you will see that the months are listed as column headers and the result are more compact.