I have a form with a drop down menu of people to filter a report of projects with the managers and up to 3 assistant managers. When I choose someone from the dropdown menu, I want all of their projects to come up on the report. Currently, only the projects that person is managing come up, not the ones they are assistant managing.
In my query that is powering this report, I have joined the manager_id number in table A to an id_num field in table B. To make the assistant managers come up in the report, I need to join the assist1_id, assist 2_id, and assist3_id to id_num also. When I join assist1_id to id_num, I get the following error:
The SQL statement couldn't be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in the SQL statement.
The way I would like to fix this problem is by creating subqueries in the SQL view, but I don’t know how to code it. If you know how or if you know a better way of doing this, please help! :)

Your table is not normalized. You have a repeating group (three assistants). Once you have more than one of something, you have many and the proper design dictates a separate table.

To get past your immediate problem, you can create a query that does what you want. You will need a union query that will normalize the data on the fly. Create separate queries that join the manager and the three assistants to the projects table so you will have 4 queries all together. Then you can turn them into a single union query that produces a recordset that contains everything you need.

Select MgrID, "Manager" As Role, other fields....
From tblA Inner Join tblProject On tblA.MgrID = tblProject.personID
Union Select assist1_id, "Assistant" as Role, other fields
From tblA Inner Join tblProject On tblA.assist1_id = tblProject.PersonID
Union Select assist2_id, "Assistant" as Role, other fields
From tblA Inner Join tblProject On tblA.assist2_id = tblProject.PersonID
Union Select assist3_id, "Assistant" as Role, other fields
From tblA Inner Join tblProject On tblA.assist3_id = tblProject.PersonID;