Relationship question: Two employees in one table?

[XelaIrodavlas]

Hi all,

Sorry if this is a dumb question but I'm struggling to find an elegant solution to this one - yet it seems so simple.

I have a table called 'PurchaseRequests' which contains two fields both of which relate to an 'Employees' table; one field is RequestedBy and the other is AuthorisedBy. (simplified example attached)

In a query, I know i could just use two copies of the Employees table and relate one to the RequestedBy and the other to AuthorisedBy. But I'm trying to represent this in the relationships tool and i'm not sure it's good practice to do it this way. My concern comes from the fact that the original employees table still keeps all it's other relationships, yet the copy is only related to Purchase requests...

Am I being stupid, or does anyone know a best way to represent this kind of relationship?

Thanks all,

 

[XelaIrodavlas]

Just brain-farting here, but I suppose technically, this presents a many to many relationship between employees and PurchaseRequests, as each PR can have one, two or zero employees, it's then down to the joining table to declare whether that employee is raising the request or authorising it...

On this basis, I need an adjoining table with 3 fields: EmployeeID, PurchaseRequestID, and a simple combo field with either Requesting or Authorising as possible answers. I can then make a 'requested by' sub form where any name entered automatically gets 'Requesting' in the 3rd field (and limit the possible no: of entries to one). then do the reverse for an 'authorised by' subform.

But, this seems needlessly complicated!

 

[CJ_London]

There is no problem with what you are suggesting - in your query have two copies of the employee table, each linked to one of your fields in purchase requests

You can also do this in the relationships view as well, but remember this is just a guide which is used to 'auto' populate the joins in a query. If you are not interested in the authoriser, don't bring through the related employee table.

You probably need to make the join for the authoriser a left join so that if the field in purchase requests has not been populated you will still get the record coming through