Count Ages By Group

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
Thank you, John!

You are the Wizard!!!

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

Tom


John Vinson said:
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?
Click to expand...

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.
Click to expand...
 
Thank you, John!

You are the Wizard!!!

On the criteria for low, should that start with greater than rather than less
than?
Click to expand...

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