Request for help with ambiguous outer join

[Julie]

Dear Access 2007 pros
I used the GUI interface to set up a query. When I do not include the last
table () it seems to work fine. When I add a fourth table, the query fails
and I get a message "Ambiguous outer join. To force one of the joins to be
performed first, create separate query that performs the first join and then
include that query in your sql statement"

What I am trying to accomplish is:
1. a one to many relationship between study and SAE on study.StudyAcronym =
SAE.SAE_studyacronym;
2. a one to many relationship between SAE and ballot on SAE.SAENumber =
ballot.ballotSAEnumber
3. a one to many relationship between SAE and SAEattach on SAE.SAENumber =
SAEattach.SAEattach_SAENumber

I checked the SQL code that is produced. It reads as follows:
SELECT study.*, SAE.*, ballot.*, SAEattach.*
FROM study RIGHT JOIN ((SAE RIGHT JOIN ballot ON SAE.SAENumber =
ballot.ballotSAEnumber) INNER JOIN SAEattach ON SAE.SAENumber =
SAEattach.SAEattach_SAENumber) ON study.StudyAcronym = SAE.SAE_studyacronym;

I would greatly appreciate any advice.
Thanks for your help. Julie

 

[Michel Walsh]

=======================
When I do not include the last
table () it seems to work fine. When I add a fourth table, the query fails
and I get a message "Ambiguous outer join.
=======================

Keep the query which works. Save it, say under the name q1.
Make a NEW query, bring q1 AND the fourth table in the query designer.
Add the joins you want between q1 (as if it was a table) and the fourth
table.

That should do the trick.


Vanderghast, Access MVP