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]

if you post some sample data/ table structure info I can help you to write a query that works.

Giacomo

 

[Faile]

Main Table (named Jobs_PO's) has general project info such as [Job#/Area], [Job #], [Area], [Phase], [Work Order #], [Purchase Order #]
A Linked Table has PO info such as [PO#], [Max PO Value], [Amt. Paid] --> Main Table PO # is linked to the Linked Table PO# to include ALL of Main and Only matching of Linked
An Imported Table to Include Project Area info such as [Job#/Area], [Committed], [Forecast] --> Main Table [Job#/Area] is linked to the Imported Table [Job#/Area] to include ALL of Main and Only matching of Imported. (NEED THIS ONE REVERSED)
There are other tables linked to [Job #] and [Phase], do you need that as well?

 

[giacomo]

I'm not seeing any problem based on what you have indicated... can you make a copy of the query using only the 3 tables you described and see if it runs for you? IF not can you post the SQL for the query using only the 3 tables?

 

[SydneyGeek]

If you have more than one outer join in a query, Access will generally spit the dummy. you need to stage it in 2 queries: the first, with one outer join, and the second, based on the first, with the other outer join.

Denis

 

[giacomo]

Based on Denis' response I'm thinking maybe I wasn't understanding the problem... This is what I picured:

[import table] -- outer join --> [main table] -- outer join -- > [po table]

is that not correct?

Giacomo

 

[Faile]

Sorry but I'm not sure what an outer join is.

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.

 

[SydneyGeek]

Look in the Design view of the query. Any table join with an arrow is an outer join. They are usually used to pull through all data from TableB, whether or note they have a match in TableA.

If you have more than one of these in your query, Access will give the error you mentioned.

Denis

 

[Faile]

So...What do we do if we need all the information in a query?

 

[SydneyGeek]

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