Ambiguous Outer Joins?

I am getting the following error message... 'The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, cre

Solution 1:

If you create a query that contains a LEFT JOIN and an INNER JOIN, Access may not be able to determine which join operation to perform first. Because the results are different depending on whether the left join or the inner join is performed first, Access displays an error message:

To correct this error, you must modify the query so that it is clear which join to perform first.

Consequently solution can be achieved by splitting this into two queries and then joining them in and additional query.

Query 1:

SELECTroute.productfam, facility.location, Asmlines.line, [18Months].[Month 1], [18Months].[Month 2], [18Months].[Month 3], [18Months].[Month 4], [18Months].[Month 5], [18Months].[Month 6], [18Months].[Month 7], [18Months].[Month 8], [18Months].[Month 9], [18Months].[Month 10], [18Months].[Month 11], [18Months].[Month 12], [18Months].[Month 13], [18Months].[Month 14], [18Months].[Month 15], [18Months].[Month 16], [18Months].[Month 17], [18Months].[Month 18], route.cycletime, route.numperprod, facilitylines.operationalhrFROM (facility INNER JOIN (ProductFamily INNER JOIN 18Months ON ProductFamily.productfamily = [18Months].[Item Type]) ON facility.location = [18Months].Location) INNERJOIN ((Asmlines INNER JOIN facilitylines ON Asmlines.line = facilitylines.line) INNER JOIN route ON Asmlines.line = route.line) ON (ProductFamily.productfamily = route.productfam) AND (facility.location = facilitylines.facility)
GROUPBYroute.productfam, facility.location, Asmlines.line, [18Months].[Month 1], [18Months].[Month 2], [18Months].[Month 3], [18Months].[Month 4], [18Months].[Month 5], [18Months].[Month 6], [18Months].[Month 7], [18Months].[Month 8], [18Months].[Month 9], [18Months].[Month 10], [18Months].[Month 11], [18Months].[Month 12], [18Months].[Month 13], [18Months].[Month 14], [18Months].[Month 15], [18Months].[Month 16], [18Months].[Month 17], [18Months].[Month 18], route.cycletime, route.numperprod, facilitylines.operationalhr, route.productfamORDERBYfacility.location;

Query 2:

SELECT route.productfam, facility.location, Asmlines.line, tableconsolidate2.SumOfyr, tableconsolidate2.SumOfyr0, tableconsolidate2.SumOfyr1, tableconsolidate2.SumOfyr2, tableconsolidate2.SumOfyr3, tableconsolidate2.SumOfyr4, tableconsolidate2.SumOfyr5, route.cycletime, route.numperprod, facilitylines.operationalhr
FROM (ProductFamily INNER JOIN (facility INNER JOIN tableconsolidate2 ON facility.location = tableconsolidate2.[Build Plant]) ON ProductFamily.productfamily = tableconsolidate2.[Prod Series]) INNER JOIN ((Asmlines INNER JOIN facilitylines ON Asmlines.line = facilitylines.line) INNER JOIN route ON Asmlines.line = route.line) ON (ProductFamily.productfamily = route.productfam) AND (facility.location = facilitylines.facility)
GROUPBY route.productfam, facility.location, Asmlines.line, tableconsolidate2.SumOfyr, tableconsolidate2.SumOfyr0, tableconsolidate2.SumOfyr1, tableconsolidate2.SumOfyr2, tableconsolidate2.SumOfyr3, tableconsolidate2.SumOfyr4, tableconsolidate2.SumOfyr5, route.cycletime, route.numperprod, facilitylines.operationalhr, route.productfam
ORDERBY facility.location;

Query 3:

Query 1LEFTJOIN Query 2