Query help grouping age ranges

[Guest]

I currently have a query that will take a person's date of birth in one
column and figure their age in years in another column. For reporting
purposes, I need to group these ages into categories: Under 18, 19-25,
26-35, 36-45, 46-55, and 56+. Right now I am just adding these up manually
but there has to be some formula that I can enter to either group these for
me and give me a total # of people in that age range (not sum of the age)

 

[Duane Hookom]

If you can be sure the age divisions will never change, you can create
monster expression in the query. A more easily maintained solution would be
to create a function that accepts the DOB, calculates the age and then
returns a group title.

I would probably use a table of age ranges like
tblAgeRanges
MinAge MaxAge AgeTitle
0 18 From 0 to 18
19 25 From 19 to 25
26 35 From 26 to 35
etc

You can then use sql to determine the age range of a person. This allows you
to easily change data rather than expressions or code WHEN the
specifications are changed.

 

[MGFoster]

I currently have a query that will take a person's date of birth in one
column and figure their age in years in another column. For reporting
purposes, I need to group these ages into categories: Under 18, 19-25,
26-35, 36-45, 46-55, and 56+. Right now I am just adding these up manually
but there has to be some formula that I can enter to either group these for
me and give me a total # of people in that age range (not sum of the age)

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

Do you mean have the age groups as columns?

SELECT
Sum(IIf(Age < 18, SaleAmount) As "Under 18",
Sum(IIf(Age Between 19 And 25, SaleAmount) As "19-25",
Sum(IIf(Age Between 26 And 35, SaleAmount) As "26-35",
Sum(IIf(Age Between 36 And 45, SaleAmount) As "36-45",
Sum(IIf(Age Between 46 And 55, SaleAmount) As "46-55",
Sum(IIf(Age > 55, SaleAmount) As "Over 55"

FROM ... etc.

Or, do you mean as row items?

SELECT
IIf(Age < 18, "Under 18",
IIf(Age Between 19 And 25, "19-25",
IIf(Age Between 26 and 35, "26-35",
IIf(Age Between 36 and 45, "36-45",
IIf(Age Between 46 and 55, "46-55",
IIf(Age > 55, "Over 55")))))) As AgeGroup

FROM ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQiOZbYechKqOuFEgEQIMbwCg2+53NZmaUxZ8jQiqTKJp7UR1Ko4AoMgW
Z2GsUWyeyXAxxps0nZxIYuYb
=E6d2
-----END PGP SIGNATURE-----