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.
-
Before
In this example, a sales query is grouped by month. If there's no sales data for a given month, that month doesn't appear. The whole column is missing. So we end up with May, June, August... etc .
Actually, another problem with this report is that the months are listed in alphabetical order (as opposed to the chronological order of each month). So it's a bit difficult to decipher all the data.
-
After
Now, there's nothing wrong with the above example, if that's all you require.
But with one small modification, this query could display all months, regardless of whether there's data for that month. Plus they'll be listed in chronological order according to date.
We'll do this below.
-
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.
-
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.
-
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.
-
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.