Access Database Ambiguous Outer Joins Error

Question

I am getting the error "The SQL statement could not be executed because it contains ambiguous outer joins" when running my query. However, I have successfully run this query in the past. I've made changes to the database, creating other queries, but had not changed this one when I started getting the error. All of the joins are Left Joins so I don't know how to resolve the issue. Thank you for any suggestions.

Below is my SQL code:

SELECT DISTINCT Baseline.SHISID, Baseline.CLIENT_ID, Baseline.LAST_NAME, Baseline.FIRST_NAME, IIf([Baseline].[AccessDental]="On","1",IIf([Baseline].[AccessDental]="No","0"," ")) AS Base, IIf([1st3M].[AccessDental]="On","1",IIf([1st3M].[AccessDental]="No","0"," ")) AS 1st, IIf([2nd3M].[AccessDental]="On","1",IIf([2nd3M].[AccessDental]="No","0"," ")) AS 2nd, IIf([3rd3M].[AccessDental]="On","1",IIf([3rd3M].[AccessDental]="No","0"," ")) AS 3rd, IIf([4th3M].[AccessDental]="On","1",IIf([4th3M].[AccessDental]="No","0"," ")) AS 4th, IIf([5th3M].[AccessDental]="On","1",IIf([5th3M].[AccessDental]="No","0"," ")) AS 5th, IIf([6th3M].[AccessDental]="On","1",IIf([6th3M].[AccessDental]="No","0"," ")) AS 6th, IIf([7th3M].[AccessDental]="On","1",IIf([7th3M].[AccessDental]="No","0"," ")) AS 7th, IIf([8th3M].[AccessDental]="On","1",IIf([8th3M].[AccessDental]="No","0"," ")) AS 8th, IIf([9th3M].[AccessDental]="On","1",IIf([9th3M].[AccessDental]="No","0"," ")) AS 9th, IIf([10th3M].[AccessDental]="On","1",IIf([10th3M].[AccessDental]="No","0"," ")) AS 10th, IIf([11th3M].[AccessDental]="On","1",IIf([11th3M].[AccessDental]="No","0"," ")) AS 11th, IIf([12th3M].[AccessDental]="On","1",IIf([12th3M].[AccessDental]="No","0"," ")) AS 12th, IIf([13th3M].[AccessDental]="On","1",IIf([13th3M].[AccessDental]="No","0"," ")) AS 13th, IIf([14th3M].[AccessDental]="On","1",IIf([14th3M].[AccessDental]="No","0"," ")) AS 14th, IIf([15th3M].[AccessDental]="On","1",IIf([15th3M].[AccessDental]="No","0"," ")) AS 15th

FROM (((((((((((((((Baseline LEFT JOIN 1st3M ON Baseline.SHISID = [1st3M].SHISID) LEFT JOIN 2nd3M ON Baseline.SHISID = [2nd3M].SHISID) LEFT JOIN 3rd3M ON Baseline.SHISID = [3rd3M].SHISID) LEFT JOIN 4th3M ON Baseline.SHISID = [4th3M].SHISID) LEFT JOIN 5th3M ON Baseline.SHISID = [5th3M].SHISID) LEFT JOIN 6th3M ON Baseline.SHISID = [6th3M].SHISID) LEFT JOIN 8th3M ON Baseline.SHISID = [8th3M].SHISID) LEFT JOIN 7th3M ON Baseline.SHISID = [7th3M].SHISID) LEFT JOIN 9th3M ON Baseline.SHISID = [9th3M].SHISID) LEFT JOIN 10th3M ON Baseline.SHISID = [10th3M].SHISID) LEFT JOIN 11th3M ON Baseline.SHISID = [11th3M].SHISID) LEFT JOIN 12th3M ON Baseline.SHISID = [12th3M].SHISID) LEFT JOIN 13th3M ON Baseline.SHISID = [13th3M].SHISID) LEFT JOIN 14th3M ON Baseline.SHISID = [14th3M].SHISID) LEFT JOIN 15th3M ON Baseline.SHISID = [15th3M].SHISID) LEFT JOIN 16th3M ON Baseline.SHISID = [16th3M].SHISID;

Answer 1

Check the JOIN(s) within 16th3M query.

Answer 2

I found somewhere that Access only allows 16 joins per query. So I deleted the last query and now it works!

Answer 3

They are all queries, not tables and yes, there are 17. I built it in the design view and the brackets or not is how Access wrote out the SQL.

Answer 4

Is this a 17-table join, or are there queries involved?

Starting an object name with a number is asking for trouble. See how in your code the same object name is sometimes bracketed and sometimes not.