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:
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.
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:
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!