Cross-tab query - dynamic row headings for subform

[paul.schrum]

I am just learning about Cross-tab queires.

I have a ct query which has time-worked as the row headings and labor
code as the column headings. I want to view this in a form, so I used
the wizard to create a datasheet form (sfm_ctab1), then made sfm_ctab1
be a subform on my main form.

It looks okay with column headings such as
000 001 003 009 etc.

Now say an employee work a few hours on labor code 002. When I run
the query there is a new column for 002. But when I view it on the
form it is not there.

What is the best way to make it so that the column headings on the
form always match the column headings in the query?

Thanks.

- Paul Schrum

 

[Allen Browne]

1. Open the query in design view.

2. Open the Properties box (View menu.)

3. Beside the Column Headings property, list all the possible headings you
could expect, e.g.:
"000", "001", "002", "003", ...

4. Save the query.

The crosstab will now generate a field for each of these, regardless of
whether there is any data to put in the column or not. Since the columns are
all present, you can go ahead and build your subform knowing that the
crosstab will supply all the possible fields.

You may also benefit from these ideas:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html