Cross-tab query - dynamic row headings for subform

  • Follow


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

0
Reply paul 2/28/2007 5:52:20 PM

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

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<paul.schrum@gmail.com> wrote in message
news:1172685140.802836.230050@a75g2000cwd.googlegroups.com...
>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
> 

0
Reply Allen 3/1/2007 10:29:44 AM

1 Replies
719 Views

(page loaded in 0.067 seconds)


Reply: