Thursday, July 24, 2014

Ambiguous Outer Joins

To see a video of this article, click here: Ambiguous Outer Join Error


Thanks to Webucator for creating this video. https://www.webucator.com/microsoft-training/access.cfm

The Outer Join can be a powerful tool for querying data in Microsoft Access. When you have only two tables, there is usually no problem. When there are more than two tables, however, using an Outer Join becomes more complicated. Sometimes Access allows it, and sometimes it gives you the not-very-descriptive "Ambiguous Outer Join" error.

Why? Well, first we'll look at what an Ambiguous Outer Join is, and then see how to correct it.
Microsoft Access has three types of joins: the Inner Join, the Right Join and the Left Join. Both the Right and Left joins are known as Outer Joins. An Inner Join shows only those records that exist in both tables. However, an Outer Join (both Right and Left) shows all of the records from one table (the Base Table) and just the matching records from the other (Secondary Table).
When Access processes a multiple table query, it needs to determine the order in which joins should be made. Should it join Table1 to Table2 first and then join Table3? Or should it do it in some other order? This is part of the Rushmore technology of the Jet engine. It tries to determine the most efficient way to process the query.

In the case of standard Inner Joins, the result set will be the same, regardless of the order in which they are joined. However, this is not the case with Outer Joins. There are times, when using an Outer Join, that the result of the query will be different depending on the order in which joins are created. In this case, Access cannot determine the order to join the tables. This is an Ambiguous Outer Join.
So how do you know when an Outer Join will result in an error? The easiest way to understand it is in terms of what you see in the Query Builder grid.

A table which participates in an Outer join as a Secondary Table (that is, the arrow is pointing *towards* it) cannot participate in either an Inner Join, or as a Secondary Table in another Outer Join. Figure 1 shows two types of queries that will result in an Ambiguous Outer Join error.
Figure 1: Two illegal Outer Join Queries

However, the table participating in the Outer Join as a Secondary Table can participate in another Outer Join if it is the Base table of the other Outer Join (that is, the arrow points *away* from it). Figure 2 shows a query that will not result in an Ambiguous Outer Join error.
Figure 2: A legal Outer Join Query
So what do you do if you need to create a query like case 1 or 2? You have to split the query into a stacked query, that is, two queries, the second of which uses the first. This is exactly what the Ambiguous Outer Join error message suggests.

Create a query joining the first two tables with an Outer Join and save it as a named query (i.e. Query1). Then, in a second query, join the first query to the third table.
Figure 3 shows how to build a stacked query.

Figure 3: Shows how to split the query into two queries to avoid an Ambiguous Outer Join.
So the Ambiguous Outer Join error is not really all that confusing. It simply means that the database wants you to decide which join it should create first. In Access, you do this by spitting the query into a stacked query.



9 comments:

7 of 69 said...

Thanks for this post. I read a half dozen on "Ambiguous Outer Joins" before finding this one. Within five minutes I had found my problem and fixed it.

Anonymous said...

Is there a way to get around creating the additional queries by just telling Access what order it should perform the outer joins in?

Roger Carlson said...

Unfortunately, no. In Access SQL, there is no way to specify which join to perform first other than stacking your queries.

Anonymous said...

Thanks very much for this - quite possible the most useful post I have read on the ambiguous outer joins issue in access and certainly the most succinct.

Anonymous said...

Agreed - good job at explaining. Other posts left me confused and frustrated.

Anonymous said...

Is there a way I can contact you roger. I am still not able to resolve my issue after reading the article. Would like to send you the picture of the joins I have.

Roger Carlson said...

You can find my contact information on my website: www.rogersaccesslibrary.com.

Anthony said...

Excellent! You should be writing the access help files!

Signatius said...

Really fantastic help to a VERY old problem I have had. Thank you very much. I am posting a further question on your website. In summary it has to do with the sequence in which you add tables to a query design window.