In Access, how to I make a query for age groups?

[Guest]

I have a database of my residents with DOB and age. I need to create queries
by age groups and I don't know how to do it, can someone please help me?

 

[Rick B]

What specifically would you like to do? What are the groups?

Also, you say you have DOB and Age. You don't actually have the age in a
field do you? If so, delete it. It is redundant and often will display the
wrong age. "Age" is a moving target; it changes once a year. Instead, add
a calculated field in your queries, forms, and reports to calculate the age
(as of right now). Then you can group on it if you want to find "ranges".

Note, the common formula for calculating age is...

DateDiff("yyyy",[Birthdate],Date())+(Format([Birthdate],"mmdd")>Format(Date(),"mmdd"))

 

[Michel Walsh]

Hi,


The easiest way is to create a table that defines the groups:

AgeGroups ' table name
Starting, Ending Label 'field name
0 18 A
18 55 B
56 65 C
66 200 D ' data sample



Note that is it possible to have overlap or super groups, if that is
applicable to your solution (as example, a super group of all "American
states" is US, and a super group of all "Canadian provinces and territories"
is Canada, or a super group of 18-200 is those who can vote, etc. Anyhow,
once your groups are defined, use the like this:


SELECT AgeGroups.Label, SUM( myInitialTable.somethingToAdd)

FROM myInitialTable INNER JOIN AgeGroups
ON myInitialTable.Age >= AgeGroups.Starting
AND myInitialTable.Age <= AgeGroups.Ending

GROUP BY AgeGroups.Label


If your groups overlap, the SUM of, here, the second column, will exceed the
initial sum of individual data, but that is intended, as, for example, a
CUBE showing SUM, per state, then for the whole US.

Hoping it may help,
Vanderghast, Access MVP