How to Create a Crosstab Query in Design View in Access
Design View offers more options for creating crosstab queries. Here's how to create a crosstab query in Design View.
A crosstab query is a special type of query that allows you to display data in a more compact way than with a normal select query.
There's a Crosstab Query Wizard, but it is somewhat limited with what you can do. In many cases, you are probably better off creating your crosstab query from Design View (or starting with the wizard, then modifying it in Design View).
Here's a quick demonstration of how to create a crosstab query from scratch in Design View.
-
Open Query Design View
Click Query Design from the Create tab in the Ribbon.
-
Select the Tables
Select each table that you need in the query and click Add to add it to the query.
Once you've added all the tables you need, click Close to close the dialog box.
In this example, I add the Orders table and the Order Details table.
-
Switch to Crosstab
Click Crosstab in the Ribbon (from the Design tab).
This switches the query to a crosstab query. You can see the options in the bottom query pane change when it's in crosstab mode (i.e. Total and Crosstab rows appear, and the Show row disappears).
-
Add Fields and Enter Criteria
As with a normal select query, you can double-click a field in the top pane to make it appear in the bottom pane.
However, in this example, we're going to use some of Access's built-in functions to aggregate some fields, and also to display another field in a more readable way.
In this example, we use the
Format()
function to display only the month portion of the Order Date. We specify the format that we want the month to appear in (i.e."mmm"
results in Jan, Feb, Mar, etc).We also use the
Sum()
function to perform a calculation on the Unit Price, Quantity, and Discount fields. -
The Result
Click !Run in the Ribbon to run the query. Alternatively, just switch to Datasheet View.
You can see that the query displays the values of the Ship City field as row headers, and the result of the
Format()
function as column headers.The result of the
Sum()
function appears wherever there's a value for that month.
Missing Fields?
See Fix Missing Fields in a Crosstab Query to display all columns regardless of whether there's data for that column.
Applying this technique on the above query will result in all 12 months of the year being displayed, even though some months don't have any data to display.
![Screenshot of a crosstab query results.](https://web.QHMit.com/pix/microsoft_access/microsoft_access_2016/howto/how_to_fix_missing_fields_in_a_crosstab_query_in_access_2016_5.png)