Multiple tables, Multiple Levels of information

[Mortal]

I am in the process of creating an Employee database, and have multiple levels of data, thus I split it to different tables.

Table 1 - General information (Name, Birthdate, phone number and Address)
Table 2 - HR information (Start Date, Employee type, Drivers License, SSN, Insurance)
Table 3 - Credential Info (Resume, Degree, CPR, First Aid)

I could merge the tables but need to add security to the HR Info. Is there a way to update all tables with one action? Should I create Lookup ID's and link them? Right now I have kept First, Last and Middle names on each table.

HELP me please...I've fallen and can't get up!

 

[Joe4]

Should I create Lookup ID's and link them? Right now I have kept First, Last and Middle names on each table.

Yes, you should create a single field ID field that you put in each table that you can use to link each table.

I was strongly suggest avoid using people's names in any kind of linking. With all the discrepancies, abbreviations, and potential for duplicates, names generally make a poor linking choice.

 

[Mortal]

Once I make the lookup, how will the tables remain updated?

 

[boblarson]

First off, you use a main form for the General Information and then you use a subform which is linked (master/child links) on the EmployeeID (which I agree with Joe that is the best way to go and usually I use an autonumber in the General information table and then it is the same name in the other tables but would be Long Integer).

The master/child links will keep the data synched. But you do not want lookups defined at table level. See here for why:
http://www.mvps.org/access/lookupfields.htm
You would use combo boxes on the FORMS for any lookups you might need (although I don't see any based on your information you have provided).

 

[Darren Bartrup]

First off, I'd make sure that no information is repeated more than once.

If someone has more than one phone number (which is very likely) I'd create a separate table for this. Keeping it in the same table would mean either having to add a new field to the table to accommodate any extra numbers & updating all your queries.

Does anyone perform more than one job?
Drive more than one type of vehicle?
All this stuff needs to go in separate tables.

Look into normalising databases - http://databases.about.com/od/specificproducts/a/normalization.htm
I haven't read through the whole link above, but seems to get the idea across.

Choose something as a unique key linking each table - maybe National Insurance number, or an autonumber field. It's got to be something that won't change. A persons name can change (if they get married, etc) and linking on that would mean changing the name in any number of tables (and no mistyping) - also Smith's a fairly common name.

Oh, and don't put calculated items in tables. E.g. Have a table for wages and keep hourly rate in one field, tax rate in another, etc. Maybe even put start and end dates on those wages and then start another record if the employees wage changes, that way you can calculate someones wages over time, and search for the maximum start date or the end date that is NULL to return the current wage.

I'd probably keep each employees hours worked in a separate table as they're liable to change with sickness, holidays, etc. Start a new record for each period of work.

lol, hard to explain how I think it should be built at 1:30 in the morning. I've probably caused more questions than answers now.

Overall I'd draw a diagram of my table structure before turning the computer on (an Entity-Relationship diagram).
Remember to put your linking fields in both tables - the unique Primary Key in one table linking to the multiple (or 1) Foreign Key records in another table. one employee - many phone numbers.

Right, hope that's understandable. Off to bed now.

Edit: Structuring your tables correctly will help no end in creating the forms and subforms that boblarson mentions.

Edit 2: My 600th post! Think I'll have a drink before bed now.

 

[jackd]

Have you looked at the free data models at

http://www.databaseanswers.org/data_models/erp/erp_hr_model.htm