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:
|
1 2 3 4 |
SELECT * FROM ([Authors] INNER JOIN [Publications] ON [Authors].id = Str([Publications].[authors].VALUE)) WHERE ( [Publications].[date] BETWEEN [Begin Date] AND [End Date] ) |
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.
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:
|
1 2 3 4 |
SELECT * FROM ([Publications] LEFT JOIN [Authors] ON [Authors].id = Str([Publications].[authors].VALUE)) WHERE ( [Publications].[date] BETWEEN [Begin Date] AND [End Date] ) |
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!