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.

  1. Screenshot of the Query Design button highlighted.

    Open Query Design View

    Click Query Design from the Create tab in the Ribbon.

  2. Screenshot of selecting the tables.

    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.

  3. Screenshot of the query in Design View with the Crosstab button highlighted.

    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).

  4. Screenshot of the query in Design View with the crosstab criteria. Close up of the query criteria.

    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.

  5. Screenshot of the result.

    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.