Query "ambiguous outer join" error

[Faile]

I am trying to run a query that contains only fields in the main table and all fields in another table. When running the query I get this 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, create a separate query that performs the first join and then include that query in your SQL statement."
If I change the relationship to include all fields in the main table and only those in the other table, it works. But I need it to work the other way.

THANKS

 

[giacomo]

Like I said before, build one query with the first outer join. Use that query in the second query, and add the second outer join.

Access is quite happy running them in sequence, it just won't do the job in a single query.

Denis

I disagree... you can do multiple outer joins in a single query.

I ran the query only using the three tables and it worked. Now I'm baffled. Don't know what to do from here. I need to include, on my report, information such as Job Name, Project Manager Names, etc which are on other tables.

It's hard to know why it's not working for you though without some additional detail. Can you post your SQL?

 

[Faile]

THIS IS THE ONE THAT GIVES ME THE ERROR MESSAGE:

SELECT [EC/ LC/ LE].Description, JOBS_POs.Notes, FBD_PROJ_MANAGERS.Manager_FName, FBD_PROJ_MANAGERS.Manager_LName, JOB_LIST.[Work Order No], JOB_LIST.Description, JOB_LIST.Location, [CUR BUD PIMS].Commit, [CUR BUD PIMS].PROJECTED, [CUR BUD PIMS].[FBD JOB/ Area], [Monthly SAP Data 1].[Maximum PO Value], [Monthly SAP Data 1].Actual, SHELL_PROJ_MANAGERS.Manager_FName, SHELL_PROJ_MANAGERS.Manager_LName, JOBS_POs.[FBD Job #], JOBS_POs.Area, JOBS_POs.Phase, JOBS_POs.[Wo Number], JOBS_POs.[PO Number]
FROM [CUR BUD PIMS] LEFT JOIN ((([EC/ LC/ LE] INNER JOIN JOBS_POs ON [EC/ LC/ LE].Phase = JOBS_POs.Phase) INNER JOIN ((JOB_LIST LEFT JOIN FBD_PROJ_MANAGERS ON JOB_LIST.FBD_Proj_Manager = FBD_PROJ_MANAGERS.Manager_Initials) LEFT JOIN SHELL_PROJ_MANAGERS ON JOB_LIST.SHELL_Proj_MANAGER = SHELL_PROJ_MANAGERS.Manager_Initials) ON JOBS_POs.[FBD Job #] = JOB_LIST.[Job No]) LEFT JOIN [Monthly SAP Data 1] ON JOBS_POs.[PO Number] = [Monthly SAP Data 1].[PO#-Line#]) ON [CUR BUD PIMS].[FBD JOB/ Area] = JOBS_POs.[FBD Job #/ Area]
WHERE (((JOBS_POs.[FBD Job #]) Like "*" & [FBD_Job_No] & "*"));

THIS IS THE ONE THAT WORKS WITH ONLY THE THREE TABLES:

SELECT [CUR BUD PIMS].[FBD JOB/ Area], JOBS_POs.[FBD Job #], JOBS_POs.[Wo Number], JOBS_POs.[PO Number], [Monthly SAP Data 1].[Maximum PO Value], [Monthly SAP Data 1].Actual, [CUR BUD PIMS].Commit, [CUR BUD PIMS].PROJECTED
FROM ([CUR BUD PIMS] LEFT JOIN JOBS_POs ON [CUR BUD PIMS].[FBD JOB/ Area] = JOBS_POs.[FBD Job #/ Area]) LEFT JOIN [Monthly SAP Data 1] ON JOBS_POs.[PO Number] = [Monthly SAP Data 1].[PO#-Line#]
WHERE ((([CUR BUD PIMS].[FBD JOB/ Area]) Like "*" & [FBD_Job_No] & "*"));

CUR BUD PIMS is the table that I need all the fields
JOBS_POs is the "main" table
Monthly SAP Data 1 is a linked excel file

THANKS

 

[giacomo]

which table does [FBD_Job_No] belong too?

 

[SydneyGeek]

I disagree... you can do multiple outer joins in a single query

Yes, that's right, depending on the setup. A sequence of Left or Right outer joins works OK. The problem I've found is with a central table that has both a Left and a Right. That's when Access throws the error, and that's when I use the 2-query approach.

Denis

 

[Faile]

FBD_Job_No is the statement that appears on the grey box when it asks what Job Number to search by.

The Job # is in a few tables as you can see, but there is no field named [FBD_Job_No].

 

[giacomo]

[quoteYes, that's right, depending on the setup. A sequence of Left or Right outer joins works OK. The problem I've found is with a central table that has both a Left and a Right. That's when Access throws the error, and that's when I use the 2-query approach.[/quote]


OK, gotcha yeah mixing lefts and rights is an issue and you have to break out into separate sql's or use sub-selects.

Faile, I must admit I'm having a little trouble following the SQL you posted. If you need a fast solution break it up into multiple queries as Denis has suggested. Then what I would do is take your working query that uses just the 3 tables and slowly add in the others one by one executing each time until you get the error. And when you do post the SQL...

hth,
Giacomo