Age Group Value

[Bill Walsh]

I need to update an AGEGROUP data field in a database based on an age. For
instance if age <11 set agegroup to "10 and under", if age = or 12, set
agegroup to "11-12" etc. I have tryed the IIF statement but to no avail.
Can someone help me with this. Both data fields are in the same database.
(Age and AgeGroup)

 

[Michel Walsh]

You can define your age groups in a table, such as:

AgeGroup FromAge UpToAgeEx
10 and under 0 11
11-12 11 13
13-15 13 16
....



(not that UpToEx is exclusive, here)


Next, make an inner join between your initial table and this one, to get
something like:



SELECT ageGroup, COUNT(*)

FROM myTable INNER JOIN ageGroups
ON myTable.Age >= ageGroups.FromAge
AND myTable.Age < ageGroups.UpToAgeEx

GROUP BY ageGroup






Vanderghast, Access MVP

 

[Marshall Barton]

I need to update an AGEGROUP data field in a database based on an age. For
instance if age <11 set agegroup to "10 and under", if age = or 12, set
agegroup to "11-12" etc. I have tryed the IIF statement but to no avail.
Can someone help me with this. Both data fields are in the same database.
(Age and AgeGroup)

There is something wrong with your table or your question.

You should NEVER have an age in a table, because the value
will be wrong everytime there is a birthday. Instead, you
should have a birthdate field and use that to calculate the
age at the time you need to see the age.

For the same reason, you should not have a age group field
in a table either.