Count Ages By Group

[Tom]

I have many records with peoples' ages. I need a query that will return this:

Age Number In Age Group
20 and younger AA
21-40 BB
41-65 CC
65 and older DD

Can anyone help with the design ofthe query?

Thanks!

Tom

 

[Tom]

Thank you, John!

You are the Wizard!!!

On the criteria for low, should that start with greater than rather than less
than?

Tom

I have many records with peoples' ages. I need a query that will return this:

Age Number In Age Group
20 and younger AA
21-40 BB
41-65 CC
65 and older DD

Can anyone help with the design ofthe query?

One general purpose way to do this involves a table, tblGroups, with
fields Low, High, and Groupname:

0 20 "20 and younger"
21 40 "21-40"
41 65 "41-65"
65 200 "65 and older"

You would include this table in your Query with no join line, but with
a criterion on Low of

<= DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

and a criterion on High of

<= <the same expression>




to calculate the age on the fly from the date of birth field DOB.

 

[John Vinson]

Thank you, John!

You are the Wizard!!!

On the criteria for low, should that start with greater than rather than less
than?

No; if it did, then people 21 years old would not be included in any
category.