Video:
In the video
The "ambiguous outer join" message appears when Access determines that your query could give different results based on which join is done first. To make the query run, you’ll need to make some changes. The best solution is usually to split the query up into pieces. That is, set it up so at least one of the joins is done in a smaller "child" query, which you then join to other tables or queries in a "parent" query. Access then performs the joins in the child query (or queries) first, then does the joins in the parent query last, eliminating the ambiguity.
Depending on how many tables you have in your query, you may need to create several child queries to avoid ambiguous outer joins. This video shows a simple three-table example, where two equi-joined tables are separated out into a child query, and then the outer join to a third table is done in the parent query. Here are the basic steps, which assume you’re already pretty familiar with create a simple select query:
-
Create a new child query that contains the tables you want joined first (in the video, these are the Companies and People tables).
-
In the child query, double-click the output fields you need so they appear the query grid.
Important: Be sure to include the join field from the original query. In the video example, Companies was joined to Regions using the RegionID field, so we include RegionID in the query grid of the child query.
-
Save and close the child query, and open the original query in Design view. This will become the parent query.
-
Delete the tables that you added to the child query, and then add the child query (Design > Add Tables (Show Table in Access 2013)> Queries, or just drag the child query from the Navigation Pane into the parent query).
-
In the parent query, establish the join as it was before, only this time to the child query.
-
Add the output fields from the child query to the query grid.
-
Save and run the parent query.
Rule of thumb for ambiguous outer joins
Ambiguous outer joins occur when you have two or more joins in a query, and one of them is an outer join that either points toward an equi-join, or there are two outer joins that point toward each other, for example, these two join configurations are ambiguous, and will need to be split into separate queries:
Table1 --> Table2 --- Table3
Table1 --> Table2 <-- Table3
Outer joins that point in the same direction, or away from other outer joins or equi-joins, are OK:
Table1 --> Table2 --> Table3
Table1 <-- Table2 --> Table3
Table1 <-- Table2 --- Table3
For more information about building queries, see Introduction to queries or Create a simple select query.
