Joining a Union

[TheSQLWasBetter]

Hi guys!

Basically, I'm trying to make a list of employees' and their managers' email addresses (along with some other information). The problem is, my boss doesn't want any contractors included unless they are managers. It's a long story as to why, but I've determined the best way to make this list is the following structure:

((Query 1 list of employees
Union
(Query 2 list of contractors
Right Join
Query 3 list of managers (use to generate contractors who are managers)))
Left Join
Query 4 list of managers (use to generate list of employees and some contractors with their managers))
------------------------------------------------------------------------------------------------------------------------------------------------------
The Union by itself works fine:
(Query 1 list of employees
Union
(Query 2 list of contractors
Right Join
Query 3 list of managers (use to generate contractors who are managers)))
------------------------------------------------------------------------------------------------------------------------------------------------------
And if you Join Query 1 with Query 4, it works fine:


(Query 1 list of employees
Right Join
Query 4 list of managers (use to generate list of employees and some contractors with their managers))

but I as soon as I try to join the union, I get an error for the JOIN clause, and Access highlights my UNION operator.

Does anyone know what might be up? I can provide the code if that helps, but there's kind of a lot of it.

Thank you!

[orange]

Can you show us the design of your tables?

[isladogs]

Can you roll back to the beginning
Are your employees/contractors & managers in one table or 3 tables?
If one table then you should be able to get what you want by filtering
If 3 tables, how are the tables linked?

You will need to provide the structure of your tables/queries for anyone to advise properly otherwise all anyone can do is GUESS