Working out age at a certain date from a DOB field

[Tangent]

I have found an expression for a query to do this. I have a table with a DOB field for each of my pupils and also a StartDate field for when they started having lessons with me.

Is it possible to use
Age:[StartDate]-[DOB]/365.25
in another field of the table 'StartAge'
If so where do I put this expression

Is there any way I can save myself calculating their age myself without using a query and if I did use a query would I have to run it for every record entered and could I append the resulting data into the StartAge field?

I'm new to Access and feeling my way and may be being a little ambitious in the related database I'm building from knowing nothing. Any help will be very gratefully received.

 

[HiTechCoach]

Welcome to the world of database programming.

How to calculate age of a person
see: http://www.mvps.org/access/datetime/date0001.htm


I would urge you to study the rules of data normalization (Click here).

One of the main rules is that you normally never store a calculated value that is based on other fields. This means that you would NOT store the the Starting Age, You would calculate it as needed.

 

[Tangent]

Thanks for your reply HiTech, although I'm not sure I understand it yet. I realised that I am trying to run before I can walk here but need to start keeping accurate records of my business starting this coming tax year.

I am a driving Instructor and in my Pupils table I have a field for their date of birth and another for the date that they started taking lessons with me.
I also want a field showing their age when they started.

This is so that a few years down the line I can find out what percentage of my pupils are male and under 21 when they come to me; or what percentage of my pupils are 17 when they start their driving lessons; or what is the average amount of lessons that the average female over 35 takes before passing their driving test.

I have lots of time to learn more and be able to set up queries to get this information later. Although my main current aim is that I will very easily be able to get my annual turnover via my Lessons table for the tax year 10/11 I want to set up the database so that I don't have to modify it too much in order to get all this information later.

It isn't a difficult thing for me to calculate myself when I enter the details of a new pupil so I can do this instead. But are you saying that under data normalisation I shouldn't have those three fields in one table: DOB; StartDate; AgeAtStart?

Many thanks

Oh and could u point me to where the rules can be found. I didn't read them when I signed up and apparently violated one of them by posting a link to my driving school web site in my first post in the introductions section and I don't what to break any more rules.

 

[Tangent]

I have another question now that I am sure will be a violation of data normalisation but will post it in a new thread

 

[Brianwarnock]

Its so long since I joined I'm not sure where the rules are but i would start by going to the FAQ as seen on the index page.

A general rule that need only be broken in special circumstances is that you do not carry calculated fields in your tables, a simple reason is data integrity.
EG somebody tells you that their birthday is 8th January 1989 and you calculate the age as it is entered, but later this is corrected to 18th January, you misheard them, you must remember to do the calculation again, but we are all human.
So no don't carry DOB, the important field, and any age calculations in your table, it is easy to do all necessary calculations in the query on which a form or report is based.

Brian

 

[Tangent]

Thanks Brian, I will do it manually then. But there is no problem having the three fields in the table if no calculations are carried out?

The DOB will be accurate unless I make and error entering it as it is contained within each pupils Driving Licence Number. But I ge what u r saying about no calculations within the tables. Thanks again.

 

[Brianwarnock]

Thanks Brian, I will do it manually then. But there is no problem having the three fields in the table if no calculations are carried out?

.

Whether you do it manually , the pupil telling you, or by define intervention, having DOB and an age is a "calculation", the data is in effect being stored twice.

Brian

 

[HiTechCoach]

It isn't a difficult thing for me to calculate myself when I enter the details of a new pupil so I can do this instead. But are you saying that under data normalisation I shouldn't have those three fields in one table: DOB; StartDate; AgeAtStart?

You would definitely store DOB and StartDate. These are not calculations.

I did not make myself clear. The only field I was saying should not be store is the AgeAtStart. The reason is because this value is calculated using DOB and StartDate.

Looking at your suggested fields:

DOB - not a calculation. It is a attribute. It can be stored if needed
Start date - not a calculation. It is a attribute. It can be stored if needed

Start Age - this is calculated from the above fields - It should NOT be stored

 

[HiTechCoach]

...
So no don't carry DOB, ...

Brian,

I am confused.

You need the DOB to calculate Age.

Why would you NOT store the DOB?

How to you calculate the DOB?

 

[Tangent]

But if I work out the age of the pupil at the time they start their lessons with me and enter it into the field AgeAtStart there are no calculations taking place within the table as I will be calculating that myself. It is static as it is the age they were at when they had their first driving lesson with me.

I can then later use that field to gather data in the future based on male and female pupils of a certain age.

So therefore the AgeAtStart is not a calculation as far as the database goes but data entered by me.

Is there still a reason I should not have that field in the Pupil table?

 

[raskew]

Hi -

I, too, am confused re DOB (see Post #9).

- DOB is a static data element and should be a field in your table.

- Same goes for StartDate.

AgeAtStart should be a calculated field (re a query). Should you, for whatever reason, determine that either DOB or StartDate is incorrect and make the necessary change(s), you would want AgeAtStart to recalculate correctly without manual intervention on your part.

HTH -

Bob

 

[Brianwarnock]

Brian,

I am confused.

You need the DOB to calculate Age.

Why would you NOT store the DOB?

How to you calculate the DOB?

Sorry I think I must have been getting my knickers in a twist at Tangents intransigence, I meant that you must carry DOB, the important field, and no age calculations.

The full sentence was

So no don't carry DOB, the important field, and any age calculations in your table, it is easy to do all necessary calculations in the query on which a form or report is based.

if I take out the bit in red I think you can see what I was getting at, but in my frustration I worded it badly.

Brian

 

[HiTechCoach]

But if I work out the age of the pupil at the time they start their lessons with me and enter it into the field AgeAtStart there are no calculations taking place within the table as I will be calculating that myself. It is static as it is the age they were at when they had their first driving lesson with me.

I can then later use that field to gather data in the future based on male and female pupils of a certain age.

So therefore the AgeAtStart is not a calculation as far as the database goes but data entered by me.

Is there still a reason I should not have that field in the Pupil table?

This is the way I look at it: Your data is what makes up the database. Access or any other software or even paper are just the storage systems.

The point we are trying top make is that you want to store the "details" not summary data. Data that is not has not already been calculated in any way is the true details.

To expand on Bob's (raskew) point, if you store DOB, then you can make other calculations with the data as needed in the future. By storing just the Start Age, you are limited in usage.

You do not have to follow this advise. It is your database and you can do it how you see best. Note the the wisdom we are sharing comes from years of experience.