Re: Calculated Field in a Table

Tech-Archive recommends: Fix windows errors by optimizing your registry


"Rick Brandt" <rickbrandt2@xxxxxxxxxxx> wrote in message
news:IidNj.1848$I55.905@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
KKallaur@xxxxxxxxx wrote:
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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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




.

Relevant Pages

  • Re: formatting a calculated number field on a form
    ... > I only used age at death as an example. ... > and heart attack, age 40 and heart attack, surgery and death, etc. ... >>bit different in that a parameter query limits the recordset before it ... I assumed that you were calculating on ...
    (microsoft.public.access.forms)
  • Re: formatting a calculated number field on a form
    ... I only used age at death as an example. ... and heart attack, age 40 and heart attack, surgery and death, etc. ... >bit different in that a parameter query limits the recordset before it ... I assumed that you were calculating on ...
    (microsoft.public.access.forms)
  • Re: Macros to run update queries
    ... Thanks for the reply, however, calculating the age at the end of the ... The age field is in the table and needs to remain there as, ... query, it would mean the addition of around 100 queries to four ...
    (microsoft.public.access.macros)
  • Re: Age calculation in Query
    ... I am trying to calculate the age of a person directly in the query. ... Directly as the control source of an unbound control: ... Where is the birthdate field. ...
    (microsoft.public.access.queries)
  • making age group query work
    ... I have an age field that is calculated from a birthdate. ... I want to count the number of records in age groups such as 10-19, 20-29, ... and Count on the Birthdate field it will show each age and count it as 1. ... Also is it possible to show the counts for all age groups in one query? ...
    (microsoft.public.access.queries)