Query an age range

[daneharnett]

Hi,
I have a table that lists dates of birth..
I would like a query that shows the number of people who fall into a certain age range eg

15-20 = 10
21-30 = 5
31-40 = 7

and so on...

I know how to get a persons age from a date of birth but im not sure on how the SQL should look to group by a range

Thanks in advance

 

[RickSpr]

Assuming 15 is the minimum age in your table (that is, that you haven't left out the range 0-15), add the following calculated column to your query:
IIf([DOB]<=DateAdd(&quot;yyyy&quot;,-20,Date),1,IIf([DOB]<=DateAdd(&quot;yyyy&quot;,-30,Date),2,IIf([DOB]<=DateAdd(&quot;yyyy&quot;,-40,Date),3,4)))

What this does is generate a column containing 1, 2, 3, or 4 depending on which range the DOB field fits into. You can then group by the generated column value and include a Count(*) column for the counts.

You might need to make slight changes to accomodate your definition of age. For example, if somebody is 20.2 years old, do they fall in the 20 age group or the 21?

The output of this query would have two columns: The generated value and the count. You'll probably need to use code in the report to translate the generated field to the labels you want (e.g. &quot;15-20&quot.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[scriverb]

Give this a try. Make a new query with the following SQL and save and name it qryAgeRanges
SELECT Switch([CalcAge]>=0 And [CalcAge]<=14,&quot;0-14&quot;,[CalcAge]>=15 And [CalcAge]<=20,&quot;15-20&quot;,[CalcAge]>=21 And [CalcAge]<=30,&quot;21-30&quot;,[CalcAge]>=31 And [CalcAge]<=40,&quot;31-40&quot;,[CalcAge]>=41 And [CalcAge]<=50,&quot;41-50&quot;,[CalcAge]>=51 And [CalcAge]<=60,&quot;51-60&quot;,[CalcAge]>=61 And [CalcAge]<=70,&quot;61-70&quot;,[CalcAge]>=71 And [CalcAge]<=80,&quot;71-80&quot;,[CalcAge]>=81 And [CalcAge]<=90,&quot;81-90&quot;,[CalcAge]>=90,&quot;90+&quot;,True,Null) AS Age_Range, IIf(DatePart(&quot;m&quot;,A![BIRTH_DATE])=DatePart(&quot;m&quot;,Date()) And DatePart(&quot;d&quot;,A![BIRTH_DATE])=DatePart(&quot;d&quot;,Date()),CInt((DateDiff('d',A![BIRTH_DATE],Date())/365.25)),CInt((DateDiff('d',A![BIRTH_DATE],Date())/365.25)-0.5)) AS CalcAge
FROM tblYourTableName as A;


Now use this SQL in a new query. Save and name it qryAgeRangeCounts:
SELECT A.Age_Range, Count(*) AS Count_Of_Range
FROM qryAgeRanges as A
WHERE ((Not (A.Age_Range) Is Null))
GROUP BY A.Age_Range
ORDER BY A.Age_Range;

Now just run the second query to obtain your results. Post back with any questions that you might have.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[RickSpr]

Bob's solution is MUCH better!

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein