Calculated Field in a Table

[KKallaur]

If I would like to store a person's age as a column in a table, could
I calculate the value of the column based off of the Birthdate field?
I was trying to do this using the defaultvalue of a column, but I'm
thinking that is not the proper way to go about it. I am an oracle
programmer, where you can do this via a trigger....does Access have a
similar type of thing that I can use?

Thanks!
Katia

 

[Rick Brandt]

If I would like to store a person's age as a column in a table, could
I calculate the value of the column based off of the Birthdate field?
I was trying to do this using the defaultvalue of a column, but I'm
thinking that is not the proper way to go about it. I am an oracle
programmer, where you can do this via a trigger....does Access have a
similar type of thing that I can use?

Thanks!
Katia

In Access (Jet) you would not have this value in your table. You would
create a SELECT query based on your table and use a calculated column in the
query to calculate the age on-the-fly. Then just use that query any place
you would otherwise have used the table. An alternative would be to just
use an expression to calculate the age on all forms and reports, but by
doing it as described in the query you only have to write the expression in
one place.

There are any number of coded things you could run say, from a form that
could calculate the age and then push that value into the table, but that
would be non-normalized and you would never be sure that the age value was
correct except immediately after setting it. By calculating it on the fly
the value is always correct.

 

[KKallaur]

Thanks, that is what I was thinking. Currently, the database design is
de-normalized, and I wanted to provide my client with a quick fix that
would make him happy before I went ahead and re-designed the schema
and included forms for data entry

Katia

 

[Evi]

In Access (Jet) you would not have this value in your table. You would
create a SELECT query based on your table and use a calculated column in the
query to calculate the age on-the-fly. Then just use that query any place
you would otherwise have used the table. An alternative would be to just
use an expression to calculate the age on all forms and reports, but by
doing it as described in the query you only have to write the expression in
one place.

There are any number of coded things you could run say, from a form that
could calculate the age and then push that value into the table, but that
would be non-normalized and you would never be sure that the age value was
correct except immediately after setting it. By calculating it on the fly
the value is always correct.

As Rick states, you really don't want to store the age in a table. After
all, your figures would all be incorrect 1 year later (or even less than a
year if their birthday is nearly due)

The function I use *in my query* for age is
AgeYr:
(Year(Date())-Year([DOB]))+(DateSerial(Year(Date()),Month([DOB]),Day([DOB]))

Date()))

DOB being date of birth

You can use this calculated field in forms and reports

Evi