sql statement could not be executed because it contains ambiguous outer joint. (1 Viewer)

[masoud_sedighy]

In my query there is a sub query, now when I join tables with inner joint there is no problem according to below code.

<code>

SELECT tblDocuments.[Owner Document Number], tblTransmittals.POI, tblPurposeofIssue.[Purpose of Issue Description], tblTransmittals.REV, tblTransmittals.[To-Owner-Tr-No], tblDocTranstoCon.TransmitToCon, tblTranstoCon.IssuedDate
FROM tblTranstoCon INNER JOIN (((tblDocuments INNER JOIN tblTransmittals ON tblDocuments.[Owner Document Number] = tblTransmittals.[Owner Document Number]) INNER JOIN tblDocTranstoCon ON (tblTransmittals.[Owner Document Number] = tblDocTranstoCon.[Owner Document Number]) AND (tblTransmittals.[To-Owner-Tr-No] = tblDocTranstoCon.[To-Owner-Tr-No])) INNER JOIN tblPurposeofIssue ON tblTransmittals.POI = tblPurposeofIssue.[Purpose of Issue]) ON tblTranstoCon.TRANSMITtoCon = tblDocTranstoCon.TransmitToCon
WHERE (((tblTransmittals.REV)=(select max(REV) from tblTransmittals as t1 where t1.[Owner Document Number]=tblTransmittals.[Owner Document Number])));


When I change relation to left joint, I received error “sql statement could not be executed because it contains ambiguous outer joint.

<code>

SELECT tblDocuments.[Owner Document Number], tblTransmittals.POI, tblPurposeofIssue.[Purpose of Issue Description], tblTransmittals.REV, tblTransmittals.[To-Owner-Tr-No], tblDocTranstoCon.TransmitToCon, tblTranstoCon.IssuedDate
FROM tblTranstoCon INNER JOIN (((tblDocuments LEFT JOIN tblTransmittals ON tblDocuments.[Owner Document Number] = tblTransmittals.[Owner Document Number]) LEFT JOIN tblDocTranstoCon ON (tblTransmittals.[Owner Document Number] = tblDocTranstoCon.[Owner Document Number]) AND (tblTransmittals.[To-Owner-Tr-No] = tblDocTranstoCon.[To-Owner-Tr-No])) INNER JOIN tblPurposeofIssue ON tblTransmittals.POI = tblPurposeofIssue.[Purpose of Issue]) ON tblTranstoCon.TRANSMITtoCon = tblDocTranstoCon.TransmitToCon
WHERE (((tblTransmittals.REV)=(select max(REV) from tblTransmittals as t1 where t1.[Owner Document Number]=tblTransmittals.[Owner Document Number])));

</code>


After that I tried to make a separate query and then use left joint, but my query is slow.
Now I would like to know is it possible with one query I use left joint and subquery.

 

[plog]

1. You shouldn't use non-alpha-numeric characters in table/field names. Makes writing code/queries that much more difficult. I would remove all dashes and spaces from your field names.

2. To post code you use brackets([ ]), not parenthesis around your code tags.

3. Even if you had, Access makes such human-unreadable SQL that you have to go through it to make it readable by adding line breaks after each clause.

4. Even after that, the way it requires parenthesis among all its joins, it would still be hard to discern for a human. So with a query this complex it would be best to show a screenshot of the design view.

5. You have to keep your joins kosher. That means you can't mix and match LEFT and INNER joins as you see fit. When you use a LEFT JOIN, every subsequent data object attached to that LEFT JOIN object must use a LEFT JOIN.

A - B - C - D - E
correct, all INNER JOINS

A -> B -> C -> D -> E
correct, all LEFT JOINS

A - B - C -> D -> E
correct, all LEFT JOINS downstream of the first LEFT JOIN

A - B -> C - D - E
incorrect, LEFT JOIN then INNER JOIN attempted downstream of it

My guess is your query did something like the last case. Or god forbid, created some kind of loop by doing something like one of the correct ones above, but adding an additional JOIN like A to D.

If you would like to post a screenshot of your design view I could confirm and help you build around it.