Microsoft Access 2003? Help Me

[Alex Cicala 0]

At the moment I am making a database for my friends phone contact information, I want to know how to make it so that I can type in the contacts Date of birth than Access can calculate the age. How would I be able to do that?

[moogie 0]

Because you can't put formulas into a database table, you would have to use an expression on a created form instead. Here's a how-to from Microsoft's website on how to do this

Calculate age from a birth date

If you want to have a person's age shown as information in a table-like setup, then I suggest you use a spreadsheet program instead.

[varalu 0]

At the moment I am making a database for my friends phone contact information, I want to know how to make it so that I can type in the contacts Date of birth than Access can calculate the age. How would I be able to do that?

There are few ways of doing this... Of what I know, there are 3 ways..

 

1. Have the age calculated and insert into the DB.

This I think is the most easy and simplest way of handling your problem. Basically, when you get the date in the front-end application (which is the form...) calculate the age and insert the age of the person in the database field instead of storing the date field. But there is a small problem associated with this... It will not get updated in the future and will be the same when the persons contact was entered... Say for example, you insert the age of a person as 20 now, it will be 20 next year also, but ideally it has to be 21. So... this option is not very good in all cases.

 

2. Calculate the age in the DB and have it as a derived field.

Here in this case, you will store the date field in the DB and will also have another filed which is a derivation of the filed which has the data of birth. So, every time it gets updated .

 

Here are the steps:

1. Get the data of birth.

2. Insert into field 1, which will hold the date of birth date values.

3. Add another filed in the DB, which is a derived column of filed 1 and calculate the age.

4. When ever there is a request, always take the second field from the DB and display it in the age form.

 

This second option will work in many of the cases... I do not see any problems here.. But make sure you use the right formula to calculate the age.

 

3. Store the date as entered, but calculate the age during display

This is another way of doing this. You store the date values for the DOB as entered, and when you display the age field iin the application you calculate the age of the particular contact and have it displayed. This is again a fool proof method just that you have to take care of the performance of the application when you do this. If you are not concerned about the speed of the application, you can work with this.

 

What I mean about the performance is... when ever you want to display the age, so you have to calculate it before you display. so, that might be a little hindrance. But this is not the case with the first 2 ways of doing it. But again, the insertion might take a little time in the first way.

 

 

So, I would recommend you go ahead with the second way, where the user will not experience any delay and everything happens in the back end.