Clicky

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];

asked 12/17/2011 03:34

murbro's gravatar image

murbro ♦♦


6 Answers:
What result you like to have?
Date, MO, AO, AO2, AO3, PhoneMO,  PhoneAO,  PhoneAO2,  PhoneAO3?
Or
Date, Operator, Phone?
link

answered

als315's gravatar image

als315

Hi
I'd like
Date, Operator, Phone

Thanks
link

answered 2011-12-18 at 00:19:08

murbro's gravatar image

murbro

Actually would like to try both but can post a further question to get the latter.
Thanks very much for the help
link

answered 2011-12-18 at 00:25:36

murbro's gravatar image

murbro

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
link

answered 2011-12-18 at 00:26:45

als315's gravatar image

als315

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.
link

answered 2011-12-18 at 01:12:12

als315's gravatar image

als315

Excellent. Thank you very much!
link

answered 2011-12-18 at 01:15:25

murbro's gravatar image

murbro

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 12/17/2011 03:34

Seen: 309 times

Last updated: 12/17/2011 05:31