variable column headers...

[nycdon]

I'm working on a report to show up to 6 people's statistics, and use their
names as column headers. The names I get from a select query identyfying
them with criteria.

The report I'd like to look as follows..

Qtr1
Consultants Bob Ted Carol Alice Joe
Billable 4 2 1 0 1
Nonbillable 1 2 3 4 2
Total xx xx xx xx xx
Weekly Avg xx xx xx xx xx

Qtr2
Billable
Nonbillable....etc

Qtr3...Qtr4...totals for year..averagaes..

I seem to be getting the data together well with crosstab queries and such,
but the headers I'm unsure of. I thought of flipping names to rows, but seems
to flow better this way I think..

Any ideas greatly appreaciated -
Thanks much!
Don

 

[Allen Browne]

The simplest solution is to list all the possible names in the Column
Headings property of the crosstab query (in the Properties box in query
design view.)

That way, the query will output the column heading for the report, even if
the person had no records in the quarter.

If the names are likely to vary in the future, it is possible to build up
the string for the PIVOT clause of the crosstab query dynamically, by
looping through the records of the salesperson table. Save the report
without any recordsource, and in its Open event, create the entire SQL
string for the crosstab, and assign it to the report's RecordSource. You
then have to assign the ControlSource of each of the text boxes and labels
to the next name, and probably set their Left and Width so they use the
space available in the Width of the report, and set the Visible property of
any unused text boxes and labels to No.

 

[Duane Hookom]

There are sample crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The crosstab.mdb has a
crosstab report with dynamic column headings