Join 4 Access SQL queries

Hi

I have 4 very similar Access SQL queries that I need to join on the fields MO, AO, AO2, AO3.
The queries look as follows:
SELECT Performance.Date, Performance.[Machine operator] AS MO, People.Phone
FROM Performance LEFT JOIN People ON Performance.[Machine operator] = People.[Operator COY];

SELECT Performance.Date, Performance.[Assistant operator] AS AO, People.Phone
FROM Performance LEFT JOIN People ON Performance.[Assistant operator] = People.[Operator COY];

SELECT Performance.Date, Performance.[Assistant operator 2] AS AO2, People.Phone
FROM Performance LEFT JOIN People ON Performance.[Assistant operator 2] = People.[Operator COY];

SELECT Performance.Date, Performance.[Assistant operator 3] AS AO3, People.Phone
FROM Performance LEFT JOIN People ON Performance.[Assistant operator 3] = People.[Operator COY];
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
als315Commented:
For first - change name of operator in queries to "Operator":
SELECT Performance.Date, Performance.[Machine operator] AS Operator, People.Phone
FROM Performance LEFT JOIN People ON Performance.[Machine operator] = People.[Operator COY];

SELECT Performance.Date, Performance.[Assistant operator] AS Operator, People.Phone
FROM Performance LEFT JOIN People ON Performance.[Assistant operator] = People.[Operator COY];

SELECT Performance.Date, Performance.[Assistant operator 2] AS Operator, People.Phone
FROM Performance LEFT JOIN People ON Performance.[Assistant operator 2] = People.[Operator COY];

SELECT Performance.Date, Performance.[Assistant operator 3] AS Operator, People.Phone
FROM Performance LEFT JOIN People ON Performance.[Assistant operator 3] = People.[Operator COY];

And use union select:
SELECT * FROM Q1 UNION SELECT * FROM Q2 UNION SELECT * FROM Q3 UNION SELECT * FROM Q4;
where Q1-Q4 - your queries
0
 
als315Commented:
What result you like to have?
Date, MO, AO, AO2, AO3, PhoneMO,  PhoneAO,  PhoneAO2,  PhoneAO3?
Or
Date, Operator, Phone?
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi
I'd like
Date, Operator, Phone

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Actually would like to try both but can post a further question to get the latter.
Thanks very much for the help
0
 
als315Commented:
Second can be done in one query:
Add table People 4 times, make your links to different copies of table and select field Phone from proper copy of table.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Excellent. Thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.