MS Access - Relationships - Help!?
I am trying to design a database for all of the employees at my place of work, to show what training they have had.
I want to be able to click on each employee's name and then go to another table showing a list of the courses they have completed, date taken, location, etc.
Do I need a separate table for each employee that is linked to the master list of employees? How do I set up this relationship?
I've tried going through the MS Access tutorial, but I am still confused.
Thanks for your replies!
- Anonymous1 decade agoBest Answer
That's a bit of a doozy...no worries though, relational databases can be complicated to grasp, but once you have the concept down you will wonder how you got along without them.
A quick run down of relational database: You need to have a unique identifier, known as a primary key, in your tables for this to work correctly. It can be numbers, letters, a combination...whatever. You can even have access assign it consecutively. The important thing is that it is unique...that way, when related tables call on a record, they can use a unique ID to find it. Make sense?
That being said..
You only need one table for employee data. It needs to have all data for the employee. The concept behind relational databases is to eliminate redundant data entry...so you will only have to enter your employees into one table. You might consider using SSN's for the unique id. When you enter a unique ID from another table, it will be exactly like entering ALL the employee data...
Now, you could take care of the training aspect one of two ways...if you have many employees taking the same training at the same time and all that, you might consider making a table called Training Sessions or something like that and using it to enter only training data, without any employee stuff...just dates that the training was offered, what it was, etc etc...
From there, you would create the Training Taken table, in which you would enter an Employee ID, and the ID of the training session they took, and store that as a record. You could call all other information from a query.
If you only have one or two employees per session, you might just skip the second step and create the Training Taken table, entering all training information there. Be sure to include a field for employee ID.
In creating the relationships....
You will create a One to Many relationship from Employee to Training Taken...with EmployeeID from Employees table being the one, and EmployeeID from the TrainingTaken table being the many. Be sure to check the Cascade and Enforce boxes...
I know I have probably confused you a bit with this...once you get into it, it might start to make more sense. Access is a tough nut to crack, you really just need to be patient and play around...and of course, come back and ask more questions if need more help!
- 1 decade ago
Create two table
employees & training
Both table should have a column EMP ID
Now go to relationship window and add two tables then drag the EMP ID col from employees to training table.