Group age data into age ranges in access query

[gingerboy]

I currently have a query which returns two columns, one detailing the age and the other returning the number of items
this is done using a count query
I need this data as ranges rather than absolute values, i.e. rather than a list of
17 years 1 item
21 years 1 item
22 years 13 items
33 years 1 item,

i want:
under 18 years 1 item
19-24 14 items
25+ 1 item

Is this possible in a sensible way? I can only think of extremely contrived ways to do this

Cheers

 

[GingerR]

since these groupings are yours, you have to define what you want.

i.e.

AgeGroup: iif(age < 18,&quot;Under 18 Years&quot;,iif(age between 19 and 24,&quot;19-24&quot;,&quot;25+&quot)

then group on this expression, and sum on the counts.

a question tho: what if someone is 18. what category are they in? )

g

 

[psemianonymous]

There's probably not a good way to do it. I'd say make a table called &quot;Age Ranges&quot; and put beginAge and endAge in there. Then calculate the age and link the tables based on &quot;MyAge BETWEEN beginAge AND endAge&quot;.

If you need an age function to calculate age based on their birthday, run a search; there are many FAQs on the subject as well.

 

[GingerR]

yes for maintenance foolio is right. say some day the age ranges change and now someone want it to be

12-15
16-18
19-23
24-27
blah blah blah

then you have to search around your db looking for every place you put code. instead this way, all you have to do is change a table.

i was gonna say that before, but chose the other way to post. advantages to both, depends on your situation.

 

[Zig1]

Hi gingerboy,

I am sure theere are many ways to solve your problem, but I think this will work:

Create a new table with two fields: Age From and Age To
Create a quiry with your regular (age) field and the new (Age To) Tables.
In the Query, select Age From, Age To, and Age
Under Age set the criteria as >[Age From] and <[Age To]
Do not join either of the tables.
Run the query and save the results as a query.

Create a second query using the above query as input.
select Age From, Age to, and Age.
Make the Query a Total Query and Count the Age Field.

 

[gingerboy]

Thanks for the helpful comments guys, I decided to go with the firs one (it seems more simple for me to implement and as the groups only appear in 2 places, editing them won't be too much hassle although hopefully i won't have to anyway. i've manged to get it working and all seems well...

cheers