Column headings in crosstab query

[Nyanzi]

Hi All,
I have created a crosstab query that gives the number of males and females in a camp and also gives the total number of people in the camp. My problem is that when i run the query, it shows the column headings as 1 and 2 (see below table) instead of showing Female and Male respectively. How can i rectify this?

CAMP Female Male Total
A 3 4 7
B 5 12 17
c 8 6 14

Thanks

 

[dhookom]

Apparently you are storing 1 and 2 as the gender value in your table. Since you probably will only have two options, I would suggest using a column heading of:
ColHead: Choose([Gender], "Female", "Male")

I could have provided a less generic response if you had provided your query's SQL View or at at least the field names.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Nyanzi]

Hi dhookom,
Thanks for the response. Well, I tried your suggestion under Column Headings in column heading properties and it gives me the correct headings but the columns are blank.
I am not storing 1 and 2 as gender values in my table..
Here is my sql:

TRANSFORM Sum(GenderDistributionAll.CountOfSECOND_NAME) AS SumOfNumbers
SELECT GenderDistributionAll.CAMP, Sum(GenderDistributionAll.CountOfSECOND_NAME) AS SumOfCountOfSECOND_NAME
FROM GenderDistributionAll
GROUP BY GenderDistributionAll.CAMP
PIVOT GenderDistributionAll.GENDER;

 

[GingerR]

please supply samples of your data.

 

[dhookom]

I don't see my suggestion in your SQL view. Try this SQL which assumes 1 is Female and 2 is Male.

TRANSFORM Sum(GenderDistributionAll.CountOfSECOND_NAME) AS SumOfNumbers
SELECT GenderDistributionAll.CAMP, Sum(GenderDistributionAll.CountOfSECOND_NAME) AS SumOfCountOfSECOND_NAME
FROM GenderDistributionAll
GROUP BY GenderDistributionAll.CAMP
PIVOT Choose([Gender], "Female", "Male");

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]