Query as source for Crosstab Query

[skydivetom]

Experts:



I have QUERY_1 which has the following (example) fields: [FIELD_1], [FIELD_2], [FIELD_3]

QUERY_1 outputs, for example, the following data:
FIELD_1 **** FIELD_2 ***** FIELD_3
ABC ***** Section_1 ***** 10
ABC ***** Section_2 ***** 20
ABC ***** Section_3 ***** 30

Now, I use QUERY_1 as my source for a crosstab query:
- FIELD_1 as "Row Heading"
- FIELD_2 as "Column Heading"
- FIELD_3 as "Value" (expression"

My crosstab query output looks as follows:
FIELD_1 **** Section_1 ***** Section_2 ***** Section_3
ABC *****10 ***** 20 ***** 30

... this works great!

However, in the event I don't have value "Section_3" in QUERY_1, I now get an error in my crosstab query. Errors reads: "Database engine does not recognize QUERY_1.[Section_3] as a valid field name or expression.

My question: How should I modify my cross tab query so that I will always get the three columns/fields (Section_1, Section_2, Section_3) even if any of these 3 values (i.e., Section_3) did NOT exist as a value in my input query QUERY_1?

I hope this makes sense.

Thank you,
EEH

[pbaldy]

See Allen's site:

http://allenbrowne.com/ser-67.html#ColHead

[skydivetom]

Perfect... thank you, Paul.

[pbaldy]

Happy to help!