Table Relationships

[tvh]

I have three fields in a table ("tblwodLeadTechnician", "tblwodAssistant" and
"tblwod2ndAssistant") that are linked to one field in another table
("tbleiEmployeeID"), however, I don't think I have the relationships set up
correctly because I'm not able to do many queries in regards to the Employee
table. The reason I have it set up this way is because ANY of our employees
can realistically fill EITHER of the three positions on any given day. And
it does change daily. I'll post the SQL to see if anyone has a suggestion to
correct the relationships.

SELECT tblWorkOrderDetail.[tblwodWorkOrder#]
FROM tblEmployees INNER JOIN tblWorkOrderDetail ON
(tblEmployees.tbleiEmployeeID = tblWorkOrderDetail.tblwod2ndAssistant) AND
(tblEmployees.tbleiEmployeeID = tblWorkOrderDetail.tblwodAssistant) AND
(tblEmployees.tbleiEmployeeID = tblWorkOrderDetail.tblwodLeadTechnician);

Thanks!

 

[Allen Browne]

Looks like you have a 3-field relationship, where you need 3 relationships.

Choose Relationships on the Tools menu.
If you see 3 lines joining the 2 tables, then delete 2 of the lines.
Then drag 2 more copies of tblEmployees into the Relationships window.
Access will alias them as tblEmployees_1 and tblEmployees_2.
Create the relations to these extra copies.
You now have 3 relations, instead of a 3-field relation.

In a similar way, add 3 copies of tblEmployees to your query.
Each one should have only one line to a fields in tblWorkOrderDetail.

Unless all 3 possitions are always filled, you will also need to
double-click these lines in your query, and turn the into outer joins.
Access will pop up a dialog giving you 3 choices, and you can choose:
All records from tblWorkOrderDetail and and matches from ...

But the best solution would be to use another table instead of the 3 joins.
Sooner or later, there will be other positions as well as those 3, and your
existing structure does not handle it. Therefore:
1. Create a table of the possible roles a worker can have.
It might have just one text field named (say) RoleID. And so you would have
records like this:
Assistant
Lead Technician
2nd Assistant

2. Remove the 3 existing fields in tblWorkOrderDetail

3. Add a table to record the people involved in a work order record, and
their roles. This table would have 3 fields:
WorkOrderDetailID foreign key ot tblWorkOrderDetail
EmployeeID foreign key to tblEmployee
RoleID foreign key to tblRole.
You can now have as many or as few workers as you need for any work order
detail record, in whatever roles are needed. You won't have to redesign the
structure to cope with future roles, and you don't have multiple relations
between pairs of tables.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have three fields in a table ("tblwodLeadTechnician", "tblwodAssistant"
and
"tblwod2ndAssistant") that are linked to one field in another table
("tbleiEmployeeID"), however, I don't think I have the relationships set
up
correctly because I'm not able to do many queries in regards to the
Employee
table. The reason I have it set up this way is because ANY of our
employees
can realistically fill EITHER of the three positions on any given day.
And
it does change daily. I'll post the SQL to see if anyone has a suggestion
to
correct the relationships.

SELECT tblWorkOrderDetail.[tblwodWorkOrder#]
FROM tblEmployees INNER JOIN tblWorkOrderDetail ON
(tblEmployees.tbleiEmployeeID = tblWorkOrderDetail.tblwod2ndAssistant) AND
(tblEmployees.tbleiEmployeeID = tblWorkOrderDetail.tblwodAssistant) AND
(tblEmployees.tbleiEmployeeID = tblWorkOrderDetail.tblwodLeadTechnician);

Thanks!