Table relationships problem !?!

[David]

I have a table with two columns 'Engagement Manager'
and 'Project Manager'. I want to link both of these
columns to my Employee table.
I set up the first relationship but when I try to set up
the second, it says 'relationship already exists'.
Is it not possible for two columns in one table to both be
related to a column in another table?

 

[Henry]

Yes you have a problem in table design.
Suggest you make a table with the following:
Table Name: tblMngtPosition
Field Name: MngtCode; Data Type Text
Field Name: MngtTitle; Data Type Text
Field MngtCode is your primary key

Populate this table with: (example)
Record 1 MngtCode = EM
Record 1 MngtTitle = Engagement Manager
Record 2 MngtCode = PM
Record 2 MngtTitle = Project Manager

Then in your Employees table add a field "MngtCode".
Make it a foriegn key related to the tblMngtPosition
primary key.

Now link the two tables on the MngtCode field.

Cheers,
Henry

 

[David]

Thanks for the answer but I'm not sure you understood my
original post.
My project table needs both engagement manager AND project
manager, so if I used your approach I think I'd still need
two links to the same table.
I have an Employee table which lists all employees whether
they be regular employees, project managers or engagement
managers, employees can have any number of roles, someone
could be an engagement manager and a project manager on
the same or different projects.
I dont see why I can't have two links to the same table,
it does not violate and relational table rule that I know
of.

 

[John Vinson]

I have a table with two columns 'Engagement Manager'
and 'Project Manager'. I want to link both of these
columns to my Employee table.
I set up the first relationship but when I try to set up
the second, it says 'relationship already exists'.
Is it not possible for two columns in one table to both be
related to a column in another table?

Yes, it is possible. Add the Employee table to the relationships grid
twice - Access will alias the second instance as Employee_1. Join
Engagement Manager to EmployeeID in Employee, and Project Manager to
EmployeeID in Employee_1.

But do consider Henry's normalization suggestion - if you have a many
to many relationship from projects to managers, it may be best to
model it as a many to many, using a third table.

 

[Greg Kraushaar]

Yep.
While you have one physical table, you have two logical tables
First is group of emplyees who are Project Manager
Second is Group of Employees who are Engagement Managers

Access Structures things this way in the relationships Window. Makes a
lot o fsense once you get used to it.