Using IIF to determine age group

[Bill]

I have tried to use the IIF command to determine a persons age group. I have
a field in the table called age from that I want to put that person in a
certain age group (another field in the same table). For instance age=18, I
want the age group field updated with "18-20". I cannot make it work can you
help me?

 

[Dirk Goldgar]

I have tried to use the IIF command to determine a persons age group. I
have
a field in the table called age from that I want to put that person in a
certain age group (another field in the same table). For instance age=18,
I
want the age group field updated with "18-20". I cannot make it work can
you
help me?

If the age group is determined by the age, as you say, then there is no need
to actually store the age group in the table. In fact, that would be
detrimental to the integrity of the table, because it would introduce the
possibility that the Age field and the AgeGroup field could be out of sync
(one updated without the other).

Instead, create a query that adds the AgeGroup field as a calculated field,
and use that query instead of the table itself wherever you need that piece
of information. SQL for such a query could look like this:

SELECT
*,
Switch(Age<10, "0-9",
Age<16, "10-15",
Age<18, "16-17",
Age<21, "18-20",
Age<30, "21-29",
Age<40, "30-39",
True, "40+")
AS AgeGroup
FROM YourTable;

I just made up the age groups, but you get the idea. You could use a series
of nested IIF functions instead of the Switch function, and it might execute
more efficiently, but it would be harder to read and maintain.

There's still a potential problem here, in that ages change all the time,
requiring updates of your table. If you can store a birth date instead of
the age, then both Age and AgeGroup can be calculated fields, and never need
updating.

 

[Bill]

Dirk, This helps a lot. Each year the participant will change so we will
have to re-enter data for the new entrants. This is being used by a junior
golf program within our county.

 

[Bill]

I used your select statement with switch and I keep getting a missing operand
when I try to save it. Any ideas?

 

[Dirk Goldgar]

I used your select statement with switch and I keep getting a missing
operand
when I try to save it. Any ideas?

The SQL statement works for me. Please post the exact SQL of your query.

 

[Paul Shapiro]

Instead of putting the groups in your sql code, I would create an AgeGroup
table with attributes ageMin, ageMax and ageGroupName. Age groups are data,
and keeping them as data makes future updates easier. Then your query to
find the age group looks for the lowest minimum age which is below the
person's age.

Here's the sql for a similar query that assigns letter grades based on a
numeric score:
UPDATE STUDENT, LETTER_GRADE SET STUDENT.letterGradeID =
[LETTER_GRADE].[letterGradeID]
WHERE (((STUDENT.letterGradeID) Is Null) AND
((LETTER_GRADE.letterGradeScoreMin) In (SELECT
Max(LETTER_GRADE.letterGradeScoreMin) AS MaxOfletterGradeScoreMin
FROM LETTER_GRADE
WHERE (((LETTER_GRADE.letterGradeScoreMin)<=[Grade]));
)));

Another suggestion is not to store age as a db field. It will definitely be
wrong in a year. Better to store birthDate and compute age, so it can always
be right.