Count Age Grouping

  • Thread starter Thread starter Nemesis_uk
  • Start date Start date
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
 
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
Click to expand...

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
Click to expand...

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]
Click to expand...


John W. Vinson [MVP]
 
Back
Top