Query on Multiple Names 1

[Jhtexas]

I have a work order table that has three one-to-many relationships with an employees table. The work order table will reference three different employees for each record using the employees primary key index (auto number) instead of a last name. When I create a query on the work order table to list what three employees are on that record I get the work order number and three numbers identifying those employees. When I add the employees table and add the last name field to the query and open the query there is no data. How do you create a query to show all the names referenced by the work order table and not just their primary key number?

Thanks in advance,
Jim

 

[PHV]

A starting point:
SELECT W.*, A.LastName AS Employee1, B.LastName AS Employee2, C.LastName AS Employee3
FROM ((tblWorkOrder W
LEFT JOIN tblEmployee A ON W.EmpID1 = A.EmpID)
LEFT JOIN tblEmployee B ON W.EmpID2 = B.EmpID)
LEFT JOIN tblEmployee C ON W.EmpID3 = C.EmpID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jhtexas]

PH,
I think I need just a little more clarification. If you can help me I’d appreciate it. I’ve been working on it, but something isn’t right.


Thanks again,
Jim

 

[PHV]

The work order table will reference three different employees for each record
EmpID1, EmpID2, EmpID3 are the 3 employees primary keys

BTW, I posted SQL code, so go to the SQL pane of the query designer window.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jhtexas]

PHV,
Are you creating a temporary table with the Select W. statement?

Thanks,
Jim

 

[PHV]

Absolutely not: W, A, B and C are alias

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jhtexas]

PHV,
I took another look at it this morning and got it to work. I appreciate your help and your patients.


Thanks,
Jim

 

[psarros]

So then give him a Starrrr!!!!!!!!!!!