How to Convert a Normal Query to a Crosstab Query in Access

Turn a select query into a crosstab query for a more readable set of results.

Sometimes a normal (select) query will return results, where, some fields need to be duplicated in order to show all the data.

A crosstab query allows you to group the query results in a way that removes the duplicate values, and presents the results in a way that makes them easier to read and decipher.

Here's an example.

Here's how to convert the first (select) query to the second (crosstab) query.

  1. Screenshot of the Query Design button highlighted.

    Open the Query in Design View

    In the Navigation Pane, right-click on the query and select Design View from the contextual menu.

    This opens the query in Design View.

    If you already have the query open in Datasheet View, you can simply switch to Design View.

  2. Screenshot of the query in Design View.

    Convert the Query

    Click Crosstab from the Query Type group in the Ribbon (from the Design tab).

    This immediately converts the query from a select query into a crosstab query.

    You can also right-click anywhere in the Diagram Pane and select Query Type > Crosstab Query from the contextual menu.

  3. Screenshot of the converted query design. Screenshot of the converted query design.

    Add the Crosstab Criteria

    You can now see that a Crosstab row has been added to the bottom Criteria Pane (and the Show row has been removed).

    The Crosstab row allows you specify which field/s will appear as row headers and which field will appear as row headers.

    The 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 this example, the Ship City field is the row header, and the Order Date field is the column header.

    Note that this field is also being formatted using the Format() function, so that the dates are displayed as months (and the results are grouped by month).

  4. Screenshot of the query result.

    The Query Result

    When we run the query, we see that the months are listed as column headers, and the results are more compact.

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.