Grouping data in unequal sized groupd

[Gerry Goldberg]

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

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT Switch(Age < 1," :01",
Age Between 1 And 5, "01:05",
Age Between 6 And 12, "06:12",
... etc. ...
Age > 84, "85: ") As AgePartition,
Sum(IIf(gender = 'M',1)) As MaleTotal,
Sum(IIf(gender = 'F',1)) As FemaleTotal

FROM table_name

GROUP BY Switch(Age < 1," :01",
Age Between 1 And 5, "01:05",
Age Between 6 And 12, "06:12",
... etc. ...
Age > 84, "85: ")
ORDER BY 1

Be sure to complete the Switch() function parameters for all ages in
both the SELECT clause and the GROUP BY clause.

I used "01" in the Switch() function so the result would sort correctly.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQm17wYechKqOuFEgEQLUYQCeMdmUOxOHZ1VnXe79eRj9rcmZiykAoMUQ
rC684iBV7xX815pQrN6mfsZc
=E0rO
-----END PGP SIGNATURE-----

 

[Danny]

you can dow load the query sample database from Microsoft site - that shows
to you how to do this