Calculate an "Age Group" from age in years

  • Thread starter Thread starter Maurice
  • Start date Start date
M

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?
 
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)
Click to expand...
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+",....
 
Maurice said:
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?
Click to expand...


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
 
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.
 
Marshall Barton said:
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
Click to expand...
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
 
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.
 
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.


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


Dale Fye said:
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 said:
:

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