Access Database Table Setup

Question

Hi, I am trying to create a simple training database for tracking employee training in our company.  I have an Employee table, a Training table, and a EmployeeTraining table for recording which employees receive which training.

The question has to do with a 4th table for department.  Each employee could be a part of multiple departments as well as each training could be required of multiple departments.  If I make a foreign key in the Employee and Training table to link to department, it creates a circular reference.  Any suggestions on how avoid this or better ways to set up the tables.  Thanks

Answer 1

Hmm, this might help...

http://www.access-diva.com/dm7.html  (Instead of Students you could rename to Employees)

Answer 2

If an Employee can be part of multiple Departments, you'll need a Join table (much like your EmployeeTraining table):

tEmployee_Department

EmployeeID

DepartmentID

If a Training course can be required for multiple Departments, you'd do the same:

tTraining_Department

TrainingID

DepartmentID

To determine the Training required for each Employee:

SELECT Training.Name FROM Training INNER JOIN tTraining_Department ON Training.TrainingID = tTraining_Department.TrainingID INNER JOIN tEmployee_Department ON tEmployee_Department.Department = tTraining_Department.DepartmentID WHERE tEmployee_Department.EmployeeID=YourEmployeeID

That is "air code", so the joins and such may have typos, but essentially you join the Training table to the Training+Department Join, and then join the Employee+Department table, and finally join the Employee table.