Count Age Grouping

N

Nemesis_uk

I have an access 2k database in which I need to count groups of
records of individuals by that age groups such as
14- 20 no of individuals
21-30 no of individuals
31-40 no of individuals
41-50 no of individuals
51-60 no of individuals
61-70 no of individuals
71-80 no of individuals
80+ no of individuals

I have both DOB and Age fields in the table

I have tried several queries but with no luck and ideas
 
J

John W. Vinson

I have an access 2k database in which I need to count groups of
records of individuals by that age groups such as
14- 20 no of individuals
21-30 no of individuals
31-40 no of individuals
41-50 no of individuals
51-60 no of individuals
61-70 no of individuals
71-80 no of individuals
80+ no of individuals

I have both DOB and Age fields in the table

Note that the Age field WILL BE WRONG for every individual in the database a
year after the person's record has been entered. Dates of birth don't change;
ages do. The Age field should not be stored at all; instead, calculate it
dynamically in a Query or control source of a textbox:

Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)
I have tried several queries but with no luck and ideas

I'd suggest using an auxiliary table Agegroups, with fields MinAge, MaxAge and
Group:

Minage Maxage Group
0 14 "Invalid Birthdate, Too Young"
14 20 "14-20"
20 30 "21-30"
....
70 80 "71-80"
80 969 "80+"

You can Join this table to a query without any join line; instead use a
criterion on the calculated Age field of
[Agegroups].[Minage] AND <= [Agegroups].[Maxage]


John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top