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”.
Step 2 – Select the table
With that done, select the table you want to analyze and click on next.
Step 3 – Select row headings
Now select the field values for the row headings that are available to you.
Step 4 – Select column headers
Next, select the column headers from the list and then click on next.
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.
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.
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.
Home