Crosstab - column names

  • Thread starter Thread starter jliu1971
  • Start date Start date
J

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
 
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
 
Back
Top