Grouping data in unequal sized groups

[ggoldberg]

I have a table that contains Age and Gender for a series of clients. I would like to be able to query this data into gender (Male or Female) totals like:
Age Total Males Total Females
<1 xxx yyy
1-5 xxx yyy
6-12 xxx yyy
13-14 xxx yyy
15-20 xxx yyy
20-24 xxx yyy
25-44 xxx yyy
45-64 xxx yyy
65-74 xxx yyy
75-84 xxx yyy
>=85 xxx yyy

I know how to do a CrossTab query, but I don't know how to group the date in unequal age ranges. Does anyone have any ideas?

Thanks,

Gerry Goldberg

 

[PHV]

You may consider an UNION query:
SELECT "<1" As AgeRange, Sum(IIf(Gender)="Male",1,0) AS [Total Males], Sum(IIf(Gender)="Female",1,0) AS [Total Females]
FROM yourTable WHere Age < 1
UNION SELECT "1-5", Sum(IIf(Gender)="Male",1,0), Sum(IIf(Gender)="Female",1,0)
FROM yourTable WHere Age Between 1 And 5
...
UNION SELECT ">=85", Sum(IIf(Gender)="Male",1,0), Sum(IIf(Gender)="Female",1,0)
FROM yourTable WHere Age >= 85

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ggoldberg]

That worked perfectly. Thanks!