Microsoft Access is confused

The other day while working on a legacy Microsoft Access database I was reminded of how bizarre Access can be. When I ran the following query:

I received a peculiar error:

Microsoft Access
The SQL statement could not 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 your SQL statement.

For a query with no outer joins, this was terribly unhelpful.

Researching the error, I found solutions like, “change all of the joins to INNER” which obviously didn’t help me. So I began trying solutions that didn’t make sense. Ultimately I found a version that worked:

By reversing the order of the tables and changing the INNER join to a LEFT join, the query works. Neither change solved the problem alone.

So next time you’re faced with a confusing Microsoft Access error message, try doing the opposite!

Leave a Reply

Your email address will not be published.