two tables. multiple relationships. is it possible?

[SirStevie3]

did a google on this and found something about "lookup fields" but didnt really understand what the writer was talking about.

I have three fields in one table that need to be related to the PK of another table.

tblProject - Engineer_ID, Producer_ID, and Project_Maner_ID
tblEmployee - Employee_ID (PK)

employees can take on any of the positions for a given project, so i'll need to have multiple employees filling up different roles for each project.

when i try to set up the relationships i get the following message:

A relationship already exists.

Do you want to edit the existing relationship? To create a new relationship, click No.

I click No, and it creates a table named tblEmployee_1. Why? is this ok?

I'm hoping someone more experienced can shed some light on my situation and offer me some advice so that i dont royally botch this database up...

Thanks guys!!

 

[plog]

Its ok, but its not 100% kosher. You have a many to many relationship between tblProject and tblEmployee. The textbook way to handle that is through a junction table (http://en.wikipedia.org/wiki/Junction_table), and a little restructuring. But doing it this way isn't a war crime.

I'll explain what you are doing first, then the junction table way. Each of your 3 fields in tblProject is related to tblEmployee, but not all at once. That means in your relationship area you will need 3 instances of tblEmployee (tblEmployee, tblEmployee_1, tbl_Employee_2) each connected to a different field in tblProject.

The junction table method is to create a table that sits between tblProject and tblEmployee to sort out the relationships between the two tables. It would look like this:

tblProjectEmployees
ProjectID, EmployeeID, Role
4, 11, Engineer
4, 29, Manager
7, 29, Producer
7, 11, Engineer

You would then remove the 3 fields from tblProjects. In relationship view you would have tblProject connected to tblProjectEmployee by ProjectID and tblProjectEmployee connected to tblEmployee by EmployeeID.

 

[SirStevie3]

brilliant! i've heard of junction tables before but never fully understood them.

thanks SO much for the clear, well written, and to-the-point reply!