Automatically Input Data In Field From Info. Input in Another Field

[trelynn]

I have created a database with an "Age" Field, and want
another field, "Age Group", to be automatically
calculated upon entering the age, for ten different age
groups. I have read and searched, but cannot find step
by step instructions on how to do this. Not sure if I
need to do a Macro, Update Query, build an expression, or
what. Can someone please help in this issue? I have
spent hours on this, and I have less than three weeks to
get this finished. Thank you for your help.

 

[Allen Browne]

Two suggestions on storing this data.

1. Don't store the age. Instead, store the date-of-birth, and calculate the
age. If you store age, your data will be always going out of date. The
person's date of birth doesn't change.

2. Store the 10 differnt age group names and the minimum age for each group
in another table, so it's easy to modify if ever needed. The table might be
called tblAgeGroup, with fields "MinAge" (number) and "AgeGroup" Text). The
records would look like this:
MinAge AgeGroup
0 Child
13 Teen
18 Adult

Now to get the age group, you need to calculate the age from date of birth,
and lookup the appropriate age group. Your data will always be correct, and
ever go out of date.

For a function to calcualte the age, see:
http://users.bigpond.net.au/abrowne1/func-08.html
For help on how to lookup a value in a field, see:
http://users.bigpond.net.au/abrowne1/casu-07.html

You will use a query to show the results of the calculated field, and it
will look something like this (in the Field row of query design grid):

AgeGrp: DLookup("AgeGroup", "tblAgeGroup", "MinAge >= " &
Age([DateOfBirth]))