Forum Discussion

bnotestine's avatar
bnotestine
Frequent Visitor
7 years ago

Ambiguous Relationships

I've got a relatively straightforward report I'm building to display Overtime amounts by Employee, by Job and by Regional Manager. I'm struggling though with my relationships trying to tie everyone's Employee ID number back to their Employee Name. I've got 3 tables:

 

Job Listing

  • Job Number
  • Regional Manager (Employee ID)
  • Site Manager (Employee ID)

Payroll Overtime Data

  • TRX Number
  • Employee ID
  • Job Number
  • Amount

Employee Listing

  • Employee ID
  • Employee Name
  • Department

So the Job Listing table has 2 Employee ID fields (Regional Manager & Site Manager) I need to tie back to the Employee Listing table to get the person's name and the Payroll Overtime Data table has 1 Employee ID field I need to tie back to the Employee Listing table to get their name. 

 

First attempt I just tried straight Many to One relationships and obviously after setting up the first one got the Ambiguous Relationships error. My second attempt I made 3 new staging tables to copy unique Employee ID values out so I could make the 3 One to One relationships from the staging tables to the Employee Listings tables but when I tried setting up the Many to One relationships to each of the staging tables I again got the ambiguous relationships error. 

 

Not entirely sure how to tie the multiple Employee ID fields back to get the Employee Names now. Any help would be appreciated. 

2 Replies