Bit of advice needed please

[robvr6]

Hi all,

This is my first post here and was just hoping for a bit of advice, what it is my dad wants me to create a small database for him and was hoping for some advice on how to tackle it. What he wants is listed below. I have some ideas but the most confusing aspect I found was how to update a persons age from the computers date.Heres what he wants and hope you can give me some advice. Thanks in advance John

Fields (free text unless otherwise stated) for people;

First name
Surname
Age
Further Education level - drop down menu for "GCSE/A level ,HNC/HND, Degree, MSc, PhD"
Degree held? - drop down menu "yes" or "no"
Chartered engineer - drop down "yes" or "no"
Professional Quals - free field for these
Skill base 1 - drop down for " Manager - Operations, Production, Maintenance, Engineering; Engineer - Mechanical, Instrument, E&I, Electrical, Rotating Equip, Process, Asset Integrity, Inspection, QA/QC; Superintendant/Supervisor - as above; and any others (think of all KPO Ops parented people)
Skill base 2 - as above
Skill base 3 - as above
Professional ladder job title
Grade - drop down menu BG3,BG4. BG5
Hay points
Date of birth
Age - can this be generated from entry above and current date?

Current Assignment job title
Current Assignment start date
Current Assignment Asset
Current Assignmnet end date
Current Assignment location (eg Cairo, Idku etc)
Currnet Assignment type - drop down menu rotation 14/14, rotation 28/28, expat

Next assignment options
Comments


Then set of data fields for posts

Post job title
Post type - drop down rotation menu as above
Likely Start date
Location
Asset
Sponsor in Asset


People Reports

People - All data
People - Listed by current job end date
People - Listed by skills (if Mechanical in any of three fields above, list him), will be repeats of names in list (as have more than 1 skill but thats OK)
People - List of those with degrees
People - List of those chartered
People - Listed by current job end date
People - Listed by current job end date
People - Listed by current job end date

Job Reports

 

[ghudson]

Welcome to the forum!

The persons age should not be stored in a table since it will change every day. That breaks one of the big rules of database normalization. The persons age should be calulated when needed like in a form or report. You should be storing the persons date of birth. There are quite a few ways to calculate a person current age. This link should offer you a few ways to calculate a person current age based on their DOB and the computers system date.

Calculating someone’s age

You can find the answer to many Access questions when you use the forums advanced Search feature.

Post back if and when you have more questions that you can not find the answers to. Good luck!

 

[robvr6]

Thanks for that I have tried a few examples regarding the date from these forums but I cannot get the age to display for the life of me.

I have tried various ways from different posts on the forums

http://www.access-programmers.co.uk/forums/showthread.php?t=49599&highlight=age+date+birth

http://www.access-programmers.co.uk/forums/showthread.php?t=62728

I have the majority of the database built althought simple to start with but I am just having trouble getting this to work. Ideally I would like to have a text box and when the date of birth is entered the age either appears on a lable of in another text box

Anybody got any ideas

Thanks in advance

John

 

[ghudson]

calculate age

Courtesy of Ricky Hicks...

If you want to calculate the age in a query ... create a "Field Expression":

Age: DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

If you want to calculate the age directly in the Control Source of a text box on a Form or Report...

=DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

"DOB" should be the name of the field that contains the "Date of Birth".

 

[robvr6]

THANK YOU VERY MUCH MY FRIEND.!!!

The second method was exactly what I was looking for. Your an absolute star.


John

 

[ghudson]

you are welcome

 

[robvr6]

just one more quickie on my form I am getting an error in the text box that the age is displayed in before I add the date of birth. Do you know if that can be removed in anyway until the dob is entered. If not I can live with that I just thought that may be easy to do.

Again thanks very much for taking time to help a newb.

John

 

[ghudson]

Without seeing how your form is designed I would not which way to direct you.

Is your form continious or single? If only one record is visible then you could leave the calculated text box unbound and code the formula in the forms OnCurrent event and also in the AfterUpdate event of the DOB text box.

If the form is set to continious then you could wrap the forumula with the IsError() function to handle the nulls or use the IsNull() formula to handle the Nulls [no DOB].

 

[robvr6]

If I sent you a link to download my database would you have time to take a look at it and give me some advice regarding the design of it?

Thanks
John

 

[ghudson]

Please post it in this thread. Strip out what you do not want to include, compact your db, zip it up and then attach it to this thread. I and anybody else in this forum will be glad to help and critique your db.

 

[robvr6]

Ok thanks, Here it is don't all laugh at once LOL. Right I have attempted to create a database that will acheive what is included in the first post of this thread. I am not very advanced with acces because I haev only skimmed the surface before trying to make this. S any input and suggestions to improve would be great. One thing I have tried to add is when the dob is entered the age will automatically update. But I cannot get this into the actual table because I think it would need to be updated on the table whenever the age changes.

Hope people can help
Thanks in advance

Rob

 

[ghudson]

I do not normally do this but you have played by all the rules with a good post for learning.

You need to use a standard naming convention with your db objects. Object names should not contain and spaces or special characters. Objects should have a defined prefix so that you can easily recognize them. Check this out for more info... Commonly used naming conventions That site is a great resource so visit it often. Here is another good article... The Ten Commandments of Access

In the tblPost table you must change the Likely Start Date field type to Date/Time.

Remove the Age field in the tblEmployees table. A persons age changes daily so why try to update a field every time a database is opened. Calculated fields should never be stored in a table. Only display calculated fields in forms or report for when they are really needed. Search the forum for database normalization for more ideas on proper database design.

I renamed the DOB text box from Date of Birth to tbDOB [using the standard naming convention] for that helps the IsNull function I mentioned below.

I renamed the Age text box to tbAge.

I locked the tbAge text box since a user should never try to manually update that text box.

I added an AfterUpdate event to the tbDOB text box to recalcuate the tbAge text box after a date is entered.
Me.tbAge.Requery

I have changed the record source of your Age field in the tblEmployees form. I changed it to what I posted above
=DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

To account for the DOB nulls I then used the IsNull() function with an immediate IIf statement [Yes, it doe have to i's, IIF]...
=IIf(IsNull([tbDOB]),"",DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB]))))

I played with some of the forms properties to give your form a better appearance.

I disagree with your use of the Form1 form to hold the tblEmployees form [a form should not be prefixed with tbl, only a table should be prefixed with tbl as a naming convention] and the tab control for it make it harder for design changes. I would use navigation buttons from a menu to allow the user to go from one form to another.

But that is my opinion. You get what you pay for.

You are off to a decent start if this is your first db.

I have attached my changes in the db2.zip file.

 

[robvr6]

THANKS VERY MUCH AGAIN FOR THE EXCELLENT RESPONSE Everything you talked about in your last post helped me understand it a lot more. It is my first database and I realised things would be wrong but its the classic learn by doing which is why there were probably a lot of mistakes on my part. But itsa the classic learn by doing thing again. Just one thing i am unsure of. My dad wants to be able to genrate a report containing all the details and I know I am probably wrong but if the age isn't stored in the table - how will I be able to extract this information out to output to the report.

Hope you are not fed up with me mithering yet lol

Thanks again

 

[ghudson]

Repeat after me... I will not store calculations in a table!!!

Say it again... I will not store calculations in a table!!!

Say it again one more time beore I answer your question... I will not store calculations in a table!!!

Create a report with the same record source that you used for your form. Create a text box in your report for the Age calculation and use the same formula I used in the tbAge text box in the form I uploaded in the db2.zip sample as the Control Source to calculate the age for each record. You have a lot of data so your report will be kinda large for each record to display the data. Only print what he needs to see. You can create more than one report and just print the type of data that is needed for each type of report.

 

[robvr6]

Right OK I have just done that but when I try to view the report i get an input box asking me to Enter Parameter value for tbDOB - Im not sure what this means. Any Ideas what Ive done wrong?

thanks

 

[robvr6]

Sorry I got it working I made a typo - I really appreciate all your help on this

thanks very much again for the help

J

 

[ghudson]

You are welcome. Good luck!

 

[robvr6]

Anyone got any info on how to add a word doc and open the doc in access

 

[ghudson]

I suggest that you create a new thread with your new question since it is not related to your original question. That should increase your chances of getting an answer.

I have never had to do it. Why do you need to do it? Did you search for an answer?

You should delete your "word doc" post in this thread once you create you new thread to avoid any confusion.

Good luck!