Dealing with the “ambiguous outer joins” message

re: Dealing with the “ambiguous outer joins” message

Bob Heifler — Fri, 03 Jul 2009 20:09:58 GMT

Nice work Colin. This will help many beginners with this common problem. What really makes it a great post is the illustrations. I will be adding more illustrations to my blog as well. Love the clean look of the Microsoft Access Team Blog as well. Maybe one day I will be chosen as a guest blogger.

re: Dealing with the “ambiguous outer joins” message

Kevin Nechodom — Fri, 03 Jul 2009 23:07:42 GMT

This works ... if you are able to add queries. Such is not always the case. I have tried techniques such as subqueries, like the following:

SELECT ...

FROM (SELECT ThisTable.Key, MoreData FROM ThisTable

LEFT JOIN ThatTable

ON ThisTable.Key = ThatTable.Key

) ResultTable

LEFT JOIN AnotherTable

ON AnotherTable.Key = ResultTable.Key

You can trick Access into doing this (which was a slight surprise to me, Kudos!!!), but DON'T let the query get munged by the QBE editor!

re: Dealing with the “ambiguous outer joins” message

Armen Stein (Access MVP) — Fri, 03 Jul 2009 23:20:46 GMT

I don't agree with the statement:

"Whenever an outer join points to a table, and that table also participates in another join of any kind, your query can't run."

In fact, if an outer join points to a table, and that table points to another table with an outer join, then your query will run.

The problem is when your arrowheads point toward each other, or toward an inner join.

The two simple rules I use to avoid ambiguous outer joins are:

1. All outer joins (arrowheads) must point away from the "main table(s)".

2. The "main table(s)" may be a single table, or may be a group of tables joined with inner joins (no arrowheads).

re: Dealing with the “ambiguous outer joins” message

Frits Scholer — Mon, 06 Jul 2009 14:41:28 GMT

If you make 2 queries, sometimes the first has to be huge. If you can resolve it by directly editing the sql code like Kevin showed the problem is easily solved.

re: Dealing with the “ambiguous outer joins” message

cdowns — Mon, 06 Jul 2009 19:11:46 GMT

Kevin: Thanks for posting that trick!

Armen: I'll pass on your comment and we'll make sure our Help and Training content is accurate on that point.

Thanks!

Chris