Calculate an "Age Group" from age in years

[Maurice]

I have a query which has a field for age at the start of the year calculated
from the date of birth. It also has the gender of the person.

I want to be able to group the people in the following bands based upon the
age at start of year (SOY) :

MS (males aged up to 39)
M40+ (males aged 40 - 49)
M50+ (males aged 50 - 59)
M60+ (males aged 60 and over)
FS (females aged up to 34)
F35+ (females aged 35 - 44)
F45+ (females aged 45 - 54)
F55+ (females aged 55 and over)

I would call this age grouping AGEBAND. It obviously incorporates the
gender in it.

If I could get this into the one query, with "M60+" (as an example) returned
against each individual, I think I could build my reports using these
groupings.

Is there anyone that would give me a simple syntax to create this that I can
paste into the field description on the query please?

 

[pietlinden]

I have a query which has a field for age at the start of the year calculated
from the date of birth.  It also has the gender of the person.

I want to be able to group the people in the following bands based upon the
age at start of year (SOY) :

MS   (males aged up to 39)
M40+ (males aged 40 - 49)
M50+ (males aged 50 - 59)
M60+ (males aged 60 and over)
FS  (females aged up to 34)
F35+ (females aged 35 - 44)
F45+  (females aged 45 - 54)
F55+  (females aged 55 and over)

One way is to use a sequence of IIFs and go in order.
iif(Gender="M",iif(Age<40,"MS",iif(Age<50,"M40+",iif
(Age<60,"M50+","M60+"))),iif(Age<35,"FS",iif(Age<45,"FS35+",....

 

[Marshall Barton]

I have a query which has a field for age at the start of the year calculated
from the date of birth. It also has the gender of the person.

I want to be able to group the people in the following bands based upon the
age at start of year (SOY) :

MS (males aged up to 39)
M40+ (males aged 40 - 49)
M50+ (males aged 50 - 59)
M60+ (males aged 60 and over)
FS (females aged up to 34)
F35+ (females aged 35 - 44)
F45+ (females aged 45 - 54)
F55+ (females aged 55 and over)

I would call this age grouping AGEBAND. It obviously incorporates the
gender in it.

If I could get this into the one query, with "M60+" (as an example) returned
against each individual, I think I could build my reports using these
groupings.

Is there anyone that would give me a simple syntax to create this that I can
paste into the field description on the query please?

This kind of thing is best done using an AgeBand table with
fields for gender, low ans high age limts for the band and
the name of the band. THen you can join this table in your
report's record source query. E,g,

SELECT sometable.*, AgeBands.Band
FROM sometable INNER JOIN AgeBands
ON sometable.gender = AgeBands.gender
AND sometable.Age >= AgeBands.Low
And sometable.Age <= AgeBands.High

 

[Duane Hookom]

PMFJI, Marsh's solution provides much greater functionality. WHEN Maurice
must change the age bands, this should not involve changing a huge nested
IIf() statement (or statements) in a query.

 

[Maurice]

This kind of thing is best done using an AgeBand table with
fields for gender, low ans high age limts for the band and
the name of the band. THen you can join this table in your
report's record source query. E,g,

SELECT sometable.*, AgeBands.Band
FROM sometable INNER JOIN AgeBands
ON sometable.gender = AgeBands.gender
AND sometable.Age >= AgeBands.Low
And sometable.Age <= AgeBands.High

Many thanks to the three of you that have responded. Whilst the additional
table approach does sound more flexible to me, I haven't the competence to
know how to approach it and therefore it looks as if I shall have to try out
the nested IIF approach in my query unless someone can point me in the
direction of an "even more simple speak" summary of the additional table
approach.

Must say I am impressed with the helpfulness of this discussion group.


Maurice

 

[Dale Fye]

1. Create a new table (AgeBands)
2. Add fields:
a. Gender (text)
b. Low (number)
c. High (number)
d. Band
3. Save table
4. Populate table:
Gender Low High Band
M 0 39 MS
M 40 49 M40+
M 50 59 M50+
M 60 110 M60+
F 0 34 FS
F 35 44 F35+
F 45 54 F45+
F 55 110 F55+

Then just create the query as Marshall described, although you will have to
do this from the SQL view, as Access is unable to display the non-equi-join
depicted.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Duane Hookom]

Dale,
Thanks for taking the time to create a quality and detailed reply.

 

[Dale Fye]

Thanks, Duane

You guys cover my back a lot of the time when I do an initial response, but
don't get to a follow-up, so I occassionally get a chance to return the
favor.

Dale,
Thanks for taking the time to create a quality and detailed reply.
--
Duane Hookom
Microsoft Access MVP

1. Create a new table (AgeBands)
2. Add fields:
a. Gender (text)
b. Low (number)
c. High (number)
d. Band
3. Save table
4. Populate table:
Gender Low High Band
M 0 39 MS
M 40 49 M40+
M 50 59 M50+
M 60 110 M60+
F 0 34 FS
F 35 44 F35+
F 45 54 F45+
F 55 110 F55+

Then just create the query as Marshall described, although you will have
to
do this from the SQL view, as Access is unable to display the
non-equi-join
depicted.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

:

Maurice wrote:

I have a query which has a field for age at the start of the year
calculated
from the date of birth. It also has the gender of the person.

I want to be able to group the people in the following bands based
upon the
age at start of year (SOY) :

MS (males aged up to 39)
M40+ (males aged 40 - 49)
M50+ (males aged 50 - 59)
M60+ (males aged 60 and over)
FS (females aged up to 34)
F35+ (females aged 35 - 44)
F45+ (females aged 45 - 54)
F55+ (females aged 55 and over)

I would call this age grouping AGEBAND. It obviously incorporates
the
gender in it.

If I could get this into the one query, with "M60+" (as an example)
returned
against each individual, I think I could build my reports using
these
groupings.

Is there anyone that would give me a simple syntax to create this
that I can
paste into the field description on the query please?


This kind of thing is best done using an AgeBand table with
fields for gender, low ans high age limts for the band and
the name of the band. THen you can join this table in your
report's record source query. E,g,

SELECT sometable.*, AgeBands.Band
FROM sometable INNER JOIN AgeBands
ON sometable.gender = AgeBands.gender
AND sometable.Age >= AgeBands.Low
And sometable.Age <= AgeBands.High

--
Marsh
MVP [MS Access]

Many thanks to the three of you that have responded. Whilst the
additional
table approach does sound more flexible to me, I haven't the competence
to
know how to approach it and therefore it looks as if I shall have to
try out
the nested IIF approach in my query unless someone can point me in the
direction of an "even more simple speak" summary of the additional
table
approach.

Must say I am impressed with the helpfulness of this discussion group.


Maurice

 

[Maurice]

Dale and everyone else,

What an excellent series of responses. I can now make progress and I'll
read the manual about "...do this from the SQL view, as Access is unable to
display the non-equi-join depicted," whatever that means. Hopefully I won't
be back - I will try!

Thank you.


Maurice

 

[Dale Fye]

Maruice,

Generally, when I create this type of query, I'll start the design in the
query grid, then change to SQL view to make the final adjustments

1.Add your main table, and the new AgeBands table to the query grid.

2.Create a join between the gender fields in both tables (Click and drag the
Gender field from your table to the Gender field in the AgeBands table

3. Create similar joins between the Age field in your table and the Low and
High fields in the AgeBands table.

4. Add whatever fields you need from your table, and the AgeBand.Band field
to the query grid. If you run the query now, you will get no records
returned because no record in your table can match on Gender, and Age = Low
and Age = High.

5. In the upper left hand corner, or on the menu bar, you should see an
option that reads "View". Click on that and then select the SQL view from
the list of options. This will show you the SQL behind your query. Modify
it as Marshall indicated in his email:

sometable.Age >= AgeBands.Low AND sometable.Age <= sometable.High

6. Now when you run your query, you should get all the records from
yourtable that meet these criteria. If you don't get some of the records
you expect, it will either be because the gender fields don't match, or the
age field doesn't match.

HTH
Dale

 

[Maurice]

Dale,

Fantastic even orgasmic! This works a treat. Many thanks to you and others
on this group providing me with simple guidance and support.


M