How to Create a CrossTab Query in Microsoft Access


In this tutorial, we will teach you how to create a crosstab Query in Access.

A cross tab query lets you analyze data on two or more variables instead of viewing your database based on one criterion.

To learn how to make such a query, follow the steps given below.

Step 1 – Go to the “create” tab

In this tutorial, we will compare the order table with the product table to know the daily sales of each product and to know the total sales of a particular day.

To create an ms Access crosstab query, move to the ‘create’ tab and click on the ‘query wizard’ button. Now let’s choose the second option i.e. “Crosstab Query Wizard”.

Move over to the “create” tab

Step 2 – Select the table

With that done, select the table you want to analyze and click on next.

Choose the table you want to analyze

Step 3 – Select row headings

Now select the field values for the row headings that are available to you.

Choose row headings

Step 4 – Select column headers

Next, select the column headers from the list and then click on next.

Choose column headers

Step 5 – Select the fields and functions

After that, you will be required to select the field and function to calculate the results. In this example we have selected Quantity and Sum from the fields and functions groups. This will show the total number of bottles sold.

Choose the fields and functions

Step 6 – Change the query name

For further use, change the name of the ms Access crosstab query so that it is easy for you to run the query again in the future. In this example, we have changed the name to ‘Tyre sold Cross Tab Query’ and clicked on finish to exit the wizard.

Give a new name to the query

Step 7 – View the table

Once you are done, a table will appear showing all the dates, sales and the total sales for that date.

And that’s it, by following the steps given above you can create a crosstab query in Access.

Look at the table