Access '13 - Does Not Recognize Field Name Error, Crosstab To Report Issue

The Background Each month I run a crosstab query which is then 'spruced up' with an Access Report. CROSSTAB SQL: TRANSFORM Sum(myTable.Field1) AS SumOfField1 SELECT myTable.Date, S

Solution 1:

Specify column headings with an IN list in the PIVOT clause.

PIVOT myTable.Field2 IN ('Option1', 'Option2', 'Option3');

Those column headings will then be included in the query result set (in the listed order) regardless of whether or not the source data includes any rows with those values.

A side effect is if the crosstab data source could ever include an Option4, it would not be included in the resulting columns. That is fine for your report since it wasn't designed to expect an Option4 column. But it might be a concern if you're using the crosstab query elsewhere.

In the report, you can use Nz() to substitute zero for Nulls from the options columns.