How to deal with variable fields to achieve constant output structure?

[G Lykos]

Greetings! Have an interesting challenge. I'm using a crosstab query to
count five categories with two states each - General structure is counts
for Case1.A, Case1.B, Case2.A, ...Case5.B. Sometimes a category will have
no counts in one or both states. When that happens, either the state
doesn't appear as a field in the crosstab results, or the entire category
doesn't appear.

I'm picking up that crosstab query with a follow-on select query to format
the data for export, and I need a constant output structure - all ten
combinations need to be present, even when there are no counts in a
particular one.

To achieve this, one tack would seem to be to figure out the syntax such
that if a particular field combination doesn't exist in the crosstab query
results, then create the field in the subsequent query nonetheless and leave
it empty. However, I can't figure out a test to use in a "IIf (field
exists, pass it thru, otherwise stub it by creating output field and leave
it empty)" - the query chokes as soon as the input field doesn't exist in
the crosstab query.

I don't want to mess with the input data. Perhaps creating a shell query or
table template and then appending to it is an option. However, how would I
automatically zap the existing data when re-running the query with fresh
data?

Thanks in advance for any ideas.

George

 

[John W. Vinson]

Greetings! Have an interesting challenge. I'm using a crosstab query to
count five categories with two states each - General structure is counts
for Case1.A, Case1.B, Case2.A, ...Case5.B. Sometimes a category will have
no counts in one or both states. When that happens, either the state
doesn't appear as a field in the crosstab results, or the entire category
doesn't appear.

Use the optional IN subclause of the PIVOT statement to specify headings which
must always appear, data or no data - it can be entered using the
ColumnHeadings property in the query grid.

John W. Vinson [MVP]

 

[G Lykos]

John, was exactly what was needed - thanks for the pointer.

George