How to Fix Missing Fields in a Crosstab Query in Access

Fix the "Crosstab Missing Field Syndrome" once and for all!

A common issue that many people run into when creating crosstab queries is that, a column will only be displayed if there's data for that group. If there's no data for that group, the column is not displayed.

You can fix this so that the column is always displayed, regardless of whether there's data for that column or not. Here's an example.

  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 with the Property Sheet button highlighted.

    Open the Property Sheet

    Click Property Sheet in the Ribbon (from the Design tab).

    This opens the Property Sheet at the side of the screen.

  3. Screenshot of the Column Headers properties. Screenshot of the Column Headers properties.

    Add Column Headings

    In the Column Headings property field, enter the column headings as you want them to appear, and in the order you want them to appear.

    You can also surround each value in double quotes, but Access will do this for you if you don't.

    Adding column headings explicitly tells Access which column headings to display. So even if there's no data for that column, it will still display.

    In this example, we enter each month separated by a comma.

  4. Screenshot of the query result.

    The Query Result

    When we run the query, we see that all twelve months are now listed as column headers, even when there's no data for that month.