Showing male and female students seperately

[Guest]

Hi everybody

I have a query which has will group the majors in the university, group the
city the students are from and count each gender (female or male)

I used

major : groupBy
city:groupBy
sex:groupBy
sex:count

I get:

Major City Sex CountofSex
CS. AAA Male 3
CS. AAA Female 5


But the way I really want to show it is :

Major City Female Male
CS. AAA 5 3

Could you please explain how to solve this ? Any tips is extremely
appreciated.

Thanks
Evrim

 

[Michel Walsh]

A crosstab, or two SUM.


TRANSFORM Nz(COUNT(*), 0)
SELECT major, city
FROM tableName
GROUP BY major, city
PIVOT Sex




or



SELECT major, city, SUM(iif(sex="m", 1, 0)) AS Male, SUM(iif(sex="f", 1 ,
0)) AS Female
FROM tableName
GROUP BY major, city



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Use IIF statements like this --
Female: Sum(IIF([Sex]="Female",1,0)) Male: Sum(IIF([Sex]="Male",1,0))