Crosstab - column names

[jliu1971]

Hi,

I have a query that looks like this:

TRANSFORM Count(mytable.var2) AS cnt
SELECT mytable.var1, count(mytable.var2) AS TOTAL
FROM mytable
GROUP BY mytable.var1
PIVOT mytable.var2;

and my result looks like this:

var1 female male
1 30 10
2 40 20
3 50 30

Is there a way I can name the columns to "column1", "column2" and so
forth, instead of female and male? Assume there are any number of
columns.

Thanks in advance,
Jenni

 

[Duane Hookom]

You can alias your column headings but it may take a few steps/queries. The
first step might be to create a query of each unique column heading value.
== qgrpColHeads ====
SELECT DISTINCT var2
FROM mytable;

Then you could number them
== qgrpColHeadNums ===
SELECT var2,
"Column" & DCount("*","qgrpColHeads","var2 >=""" & var2 & """") As ColNum
FROM qgrpColHeads;

Then include qgrpColHeadNums in your final crosstab joining the var2 fields
and using ColNum as the column heading.
Then join