Ambiguous Outer Join Error

[Topflite66]

I'm fairly new to working with Access so please bare with me if it don't explain exactly what I am attempting. I have 4 tables that I am working with in a query, please see attachment. I have the tbl_Model set up as a one-to-many relationship with the tbl_VehicleDetails table. When I run the query it displays only the references in the Vehicle Details table with the associated Models. I would like create an outer join so that all of the data in the Model table displays even if there has not been an entry in the VehicleDetails table. When I right click on the Join Property Line and select option 2 for this to happen I receive an error message : The SQL Statement could not be executed because it contains ambiguous outer joins. I don't quite understand what that means. I've watched several videos accomplishing the same thing as what I am trying to do and theirs works. Please help. Here is the sql also:

PARAMETERS [Please Enter a Model Year] Text ( 255 );
SELECT tbl_MdlYr.MdlYr, tbl_Model.Model, tbl_Model.ModelID
FROM tbl_Model LEFT JOIN (tbl_MdlYr INNER JOIN (tbl_VehicleDetails INNER JOIN tbl_OrderDetails ON tbl_VehicleDetails.OrderID = tbl_OrderDetails.OrderID) ON tbl_MdlYr.MdlYrID = tbl_VehicleDetails.MdlYrID) ON tbl_Model.ModelID = tbl_VehicleDetails.ModelID
GROUP BY tbl_MdlYr.MdlYr, tbl_Model.Model, tbl_Model.ModelID
HAVING (((tbl_MdlYr.MdlYr)=[Please Enter a Model Year]));



Thank you

[ranman256]

yea, this is always a pain...
if you have outer join w more than 2 tables, all joins must flow outer join in one direction.

you cant have the 1:1 join, change the join to 1:many. (many tOrderDetails)
in your example, all data must flow right to left, 1 to many.

(even tho the relationship is 1:1, the altered join will still produce results)

[Topflite66]

Thank you ranman256 for responding so quickly. So I changed the relationship between the VehicleDetails table and the OrderDetails table so that it was a 1-to-many with the VehicleDetails table being the one.

I initially removed the fk from the OrderDetails from the OrderID, went into the query and received the same message. So I re-added the fk to the OrderID in the OrderDetails table and got the same message.

One thing I should point out is that I do have several other tables Series, BdyCd, PepCd, etc that are in a 1-to-Many relationship with the VehicleDetails table. For this particular query though, they do not come into play. Not sure if that could be causing the issue? The other thing I tried was to go into the relationship and change all the Join Types to option 2. That didn't seem to change anything either

It looks so easy in the videos. LOL Any other thoughts? Thanks again.

[isladogs]

As Rahman said previously, if using outer joins, ALL MUST have the same 'flow direction'
In your case, you need the join from tblmidyr to tblvehicledetail to be changed to outer join

Or use inner joins for each link

[Topflite66]

As Rahman said previously, if using outer joins, ALL MUST have the same 'flow direction'
In your case, you need the join from tblmidyr to tblvehicledetail to be changed to outer join

Or use inner joins for each link

I guess I am missing a step here.

As mentioned in my previous post, I went into the relationship window and opened relationship between all tables that are related to the Vehicle Details table. All are set up as being 1-to-many with the many being the Vehicle Details table and the other tables being the 1. I also ensured that I checked option 2 in the Join Type which would instruct Access to include all the records from the 1 table, i.e. Mdlyr, Mdl, Series, etc., and only the records from the Vehicle Details table where the joined fields were equal. I then went into the query and ensured that option 2 was selected for the Model table and I am still receiving the ambiguous outer joins error. When I look at the sql it shows only that they are Left Joins. I understand the concept of what each type of Joins are for. I'm just not sure how to change them to a Left Outer Join?

Also I did a test. I removed all of the relationships from the Vehicle Details table. So it was in the relationship window related to no other table. I then created a new query with only the Vehicle Details table and the Model Table. I created a relationship with the Left table being the Model table and the right table being the Vehicle Details table only. Then selected option 2 in the join properties. This time the query actually ran and did not give me the error message. So I added the MdlYr table to the query. I created a relationship so that the MdlYr table was on the left and the Vehicle Details table was on the right. Then select option 2 in the Join Properties. I tried to run the query and received ambiguous outer joins error. So I deleted the relationship line from the MdlYr table to the Vehicle Details table. Tried to run the query and received the same error. So it seems that as soon as I add a second table to the query I receive that error.

Sorry for being so dense on this. Thank you.

[Micron]

Often the message can be attributed to a scenario where the joins portion is like this:
From A left join something on something = ... inner join this on that

So are the joins like what you expect above (red on blue; green on purple), or are they blue on green? That might be a rather simplistic attempt at explaining, but you have to also take the portion of the sql that comprises the SELECT portion, as that part "groups" the tables into an instruction on all the tables involved. Even if you look at your sql and try to mix and match as I have, you might find you are unable to do so.

If you remove the mdlYr table from the above and still get the message I'd be surprised. By not having a join line but including the table, you've made it worse by implying a Cartesian join - between it and both of the other two tables.

[isladogs]

Thought I'd illustrate the previous post with examples

There are three types of join - INNER , OUTER (Left or Right) or CARTESIAN (no join)

The first screenshot shows 4 tables linked with an outer join done correctly - it runs without error



The above would also run correctly if all were INNER joins
Furthermore it will work if the first join was INNER but not the other two
AND again OK if the first two ARE INNER but not the third

BUT it will gives the ambiguous join error if the first and third joins are INNER but the second is OUTER



The next screenshot shows the same 4 tables but this time one table has an incorrect flow direction - this gives the ambiguous outer join error



The third screenshot shows that join removed thus creating a 'cartesian join' - this also gives the ambiguous outer join error



Hope that helps

[Topflite66]

Thought I'd illustrate the previous post with examples

There are three types of join - INNER , OUTER (Left or Right) or CARTESIAN (no join)

Thank you for the visual. I do tend to understand things better if I can see it. I think I understand now what's causing the error. As long as the flow of the joins goes in the same direction it will work.



However when I add the second table, Model, to the query Access doesn't know which join it is supposed to process first either the MdlYr or the Model table joins and that causes the error. Correct?


So I'm guessing that the second part of the error statement is leading me to the answer to this problem. I'm thinking a subquery would work in this case? Create a query like the first example and then create another query and add the first query as a sub query?

Thanks again.

[isladogs]

Almost - not a subquery as such

Create AND save the query in your first screenshot including as many fields as will be needed later - lets call it qryVehicvleOrderMdYr or similar
This 'forces one of the joins to be performed first'

Now create a second query joining tbl_Model to qryVehicvleOrderMdYr using an outer join
Add all fields required - DONE

[Topflite66]

Almost - not a subquery as such

Create AND save the query in your first screenshot including as many fields as will be needed later - lets call it qryVehicvleOrderMdYr or similar
This 'forces one of the joins to be performed first'

Now create a second query joining tbl_Model to qryVehicvleOrderMdYr using an outer join
Add all fields required - DONE

Excellent. That solves my problem. Thank you all for your help!!

[isladogs]

You're welcome. Glad to have helped.

[isladogs]

Just looked at your table diagrams again.
Tables with a PK and one other field are rarely needed

What's in tblmdlyr if it's just a year such as 2017 then that could go in tblvehicledetails.
Similarly tblmodel???

You may only need 2 tables