Access 2007 Relationships

Hi Experts,

Just a quick simple question regarding Access 2007 relationships.  I'm super green to this stuff - just spent about a week on it.  I'm a little confused when it comes to how the database should be set up.  What I've read is that I shouldn't have data in multiple table, however I've also read that when data is repeated it should have it's own table...  I don't understand what I just typed, so I understand if you don't.

To the point.  All I'm looking for is whether the attached screen shot of my relationship set up looks accurate of out to lunch.

I will accept the answer 'accurate' or 'out to lunch' - If the answer is 'out to lunch', which I'm assuming it will be a brief explanation as to why would be appreciated.

The reason I'm awarding 500 points is because of the read you just did - and the explanation if 'out to lunch' needs to be understood.

Who is Participating?
Ok.  I'm going to give this a shot.  
You are correct that you shouldn't have the same data in more than one table and when data is repeated it should have its own table.  An example of having the same data in more than one table is Training Received and Type which appear in both your Training Module Log table and your Training Modules Info table.  An example of repeated data that should be moved into its own table is when you have 5 buildings and you keep entering the names of the buildings into a table.  This data should be placed in its own table with a Building_ID and Building_Name.  The Building_ID is used in place of typing the name repeatedly.  This ensures that if you change the name of the building, you only have to change it in one place.  The associated ID stays the same so you don't have to change it every time it is used in the main table.
Specifics for your database --  I would change the tables to the far left as follows:  
Building Info -- add a field called Building ID and use this ID in the Employee Info table.
Position Info -- Add a field called Position ID and use this ID in the Employee Info table.
Status Info -- Add a field called Status ID and use this ID in the Employee Info table.
I would eliminate the Shift table unless you have alot of weird shifts that people can work.  Usually there are only shifts 1, 2 and 3.  If you have other shift information such as varying pay rates, then by all means add a Shift ID to the table and any other information related to a shift and use the Shift ID in the Employee Info table.
You are duplicating Building and Shift in Employee Info and Attendance Log.  If the employee always works in the same building, I would put Building with the Employee Info, if not, then put it in the Attendance Log table.  Remove shift from Employee Info and leave it in Attendance Log.  Eliminate Employee Info_2, Employee Info_1 and Type.  
Change Employee Name in Attendance Log to Employee ID.  Employee ID is a foreign key to the Employee Info table and should be of the same data type as ID in the Employee Info table.  Do the same with Employee Name in the Training Module Log table.
Training Received = Training Module so Type = Type.  Eliminate the duplicate Type from the Training Module Log and leave all other fields in place.
The relationships themselves seem to be fine.  If there's anything you don't understand, just ask.
Jeffrey CoachmanMIS LiasonCommented:
There is no easy way to tell just by looking at the screenshot.

You need to tell us what this system is supposed to be doing.

The other factor here is that you have pretty much built the entire dastabase without being "Sure" of anything.

Why not just start out with a simple question, stating what it is you want the database to do, and ask for a "Basic" table structure.

Also this database seems to be tracking two seperate things
1. Employee Training
2. Employee Attendance, (apparently not related to Training)

Again, these are two seperate issues.

Any Expert here can see that if you ultimate goal here is to create an "Employee Management Database", this can end up being quite complex.

So stay focused and clearly state what you are trying to create here.


Jeffrey CoachmanMIS LiasonCommented:
Please leave this question open for a few days so that other Experts can post their opinions.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:

I have requested that the Q be re-opened, as vohalloran seems willing to help.


NEVAEHSINAuthor Commented:
First off - Boag2000 - Very commendable of you :)

Alright - I'm going to take a stab at answering your reply.  I've taken the liberty of attaching the db i'm working on - keep in mind it is still incomplete and there will be a lot more tables added.  Before reading this I had rebuilt it and incorporated some of what you had suggested as far as using ID's (Autonumber) rather than text fields.   The reason I used text fields rather than ID's was because I was unable to put (for example) the employee name in the tblattendance from the tblemployeelist - still can't - but that will be another question later.  Also, the reason behind having Training received and type in multiple tables was so that it was visible in the tables - these are selected from lists that are querried from the original tables.  The shift table I think I need because it will eventually tie into tables that reflect productivity and efficiency.

In regards to the training aspect - We have dozens of different courses and approximately 100 employees that get trained on different dates - the type comes into play because there are different categories of training (i.e. Policy, procedure, safety, etc.) and I wish to keep these categorized with details.

Please let me know your opinion on the relationships on what is set up so far - and keep an eye out for me as I'm really new to this stuff and when I have to figure out all the forms for this mess there will be lot's of points up for grabs :)

Jeffrey CoachmanMIS LiasonCommented:
In the mean time, please study the advice posted by vohalloran.

NEVAEHSINAuthor Commented:
Oh I have been, and still doing so :)
NEVAEHSINAuthor Commented:
I'm not going to leave it open as you have hit the nail right on the head.  This is the beginnings of an Employee Management Database and this is just the beginning of it - there are more tables that go into productivity, discipline, customer tracking, etc.  You're also correct in stating that I've built the database without being "sure" of anything :)

I'm going to start over and go in baby steps - asking you experts questions along the way.

Thanks for opening my eyes a little :p
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.