Pivot Crosstab Query Question

[mfd777]

I have a simple crosstab query. I need the column headings to be fixed. I have typed these into the column headings property of the field in the query. My question is that I would like the column headings to be read from a table rather than hardcoded into the query as the number of column headings may increase. I know that I could leave them blank and they would auto generate, but I need more control than that (i.e. would like them to appear even if there is no value for the date range specified).

 

[MichaelRed]

I generally use a select query which is "guarnteed" to include the fields which I want in the crosstab query as an outter join with the crosstab query to accomplish this. You could just use a table, If you absoloutly want to set the field headings. You should note, however, that using just a table whith the outter join may keep some fields from appearing in the final result.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[mfd777]

Micheal thanks for you reply. Perhaps you could suggest what I am doing wrong. Imaging there is a table called cctbl with one field called cc (i.e. cost centre). In the designe of the crosstab query ( based on another table) I am trying to set the column name as follows:
PIVOT maindata.cc_name in
(select cc from cctbl

The select from the cctbl create one column heading with a title of "select cc from cctbl

 

[MichaelRed]

this is not clear to me, but I think you need to qualify which fields to select from 'cctbl' in the subquery:
[tab](Select cc from cctbl.cc)

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over