Issues with query (linked to crosstab) when a value (for column heading) is missing

[skydivetom]

Hello:

I'd like to get some assistance with a query linked to a crosstab query. I'll provide some background first.

Background (file "SampleData_v01"):
- Attached sample DB (v01) includes table [T_Sample_Data_1] including 17 sample records.
- In v01, field [CONCURRENCE_FINAL] has 3 distinct values: "---", "Concur", and "Non-Concur" where latter value exists in sample record #17 only.
- Crosstab query "Q_SampleData_CT" uses field [CONCURRENCE_FINAL] for the column heading.
- Now, subform (SF) query "Q_SampleData_SF" works without issues as I have values (i.e., "---", "Concur", and "Non-Concur") for all column headings.
- FYI... please note that the query's criteria is set to "ABC" & "Alpha" but the query criteria is NOT related to the underlying issue.

... again, all works great in version 01.



Now, let's review version 02 (copy of v01).

Current Issue:
- Table [T_Sample_Data_1] now includes only 16 sample records (1 less than v01). That is, I purposefully deleted record #17 in this version (that was the only record containing "Non-Concur").
- While crosstab query "Q_SampleData_CT" still executes, I now experience an issue with the 2nd query (subform) "Q_SampleData_SF".
- Specifically, the 2nd query no longer opens given I do NOT have any values for "Non-Concur" in my sample data any longer.

My question:
- How do I need to modify query "Q_SampleData_CT", so that it still displays the "Non-Concur" column even if the source table has zero values for it?
- That is, I somehow just need a, e.g., "dummy placeholder" for the "Non-Concur" column where value count should be equal to default value of, e.g., zero (0) across all row values.

[June7]

Thread is marked Solved. Is it?

If you want to force column headings to display even when there is no data for that category, must specify headings with IN() clause. Review http://allenbrowne.com/ser-67.html#ColHead

[skydivetom]

Yes, it is solved.

I found solution where I could force field headings in query's column heading properties. Thank you.

[orange]

Tom,

You might want to post a sample showing the related code and some descriptive material. It might be helpful to others. I'd suggest the Code Repository as the appropriate spot.
Good luck.

[skydivetom]

Orange:

Please see attached screenshot. Entering crosstab's field headers (e.g., "A", "B", "C", "D", "E") into the column headings will include all fields in the crosstab query even if there are no values for fields/column, e.g., "B" in the data source.