ambiguous outer joins

[Rosemary]

I'm trying to write a query to gather information for a report. When I first
did it, I got the message that the SQL statement could not be executed
because it contains ambiguous outer joins. I looked at the example in the
Help, which is a very simplistic example and tried to apply it by splitting
my query into two queries.

Bill_One Query calls Bill_Two Query:

Bill_One Query:
SELECT bl_Detail.Detail_id, bl_Detail.Bill_id, bl_Detail.Category_id,
bl_Detail.Category_Cost_no, bl_Detail.Qty_no, bl_Detail.chk_Picked,
bl_Detail.chk_Has_SubCat, bl_Detail.chk_Has_Comment,
bl_Detail_SubCategory.Detail_id, bl_Detail_SubCategory.SubCategory_id,
bl_Detail_SubCategory.SubCategory_Cost_no, bl_Detail_SubCategory.chk_Picked,
bl_Comment.Detail_id, bl_Comment.Comment_tx, bl_Comment.Sort_id,
bl_Comment.Delete_flg, bl_Bill.Cust_id, bl_Bill.Version_tx, bl_Bill.FY_id,
bl_Bill.DOIM_id, bl_Bill.Bill_dt, bl_DOIM.DOIM_From_nm,
bl_DOIM.DOIM_Addr1_tx, bl_DOIM.DOIM_Addr2_tx, bl_DOIM.DOIM_Addr3_tx,
bl_DOIM.DOIM_City_tx, bl_DOIM.DOIM_State_tx, bl_DOIM.DOIM_Zip_cd,
bl_DOIM.DOIM_Fax_no, bl_DOIM.DOIM_POC_nm, bl_DOIM.DOIM_Email_tx,
bl_Customer.Cust_Abbrev_nm, bl_Customer.Cust_nm, bl_Customer.Cust_To_tx,
bl_Customer.Cust_Addr1_tx, bl_Customer.Cust_Addr2_tx,
bl_Customer.Cust_City_tx, bl_Customer.Cust_State_tx, bl_Customer.Cust_Zip_cd,
bl_Customer.Cust_UIC_cd, bl_Customer.Cust_ISA_cd, [Bill_Two Query]
FROM bl_DOIM LEFT JOIN (bl_Detail_SubCategory LEFT JOIN (bl_Comment LEFT
JOIN (bl_Customer LEFT JOIN (bl_Bill LEFT JOIN (bl_Detail LEFT JOIN
([Bill_Two Query] ON bl_Detail.Category_id = bl_Category.Category_id) ON
bl_Bill.Bill_id=bl_Detail.Bill_id) ON bl_Customer.Cust_id=bl_Bill.Cust_id) ON
bl_Comment.Detail_id=bl_Detail.Detail_id) ON
bl_Detail_SubCategory.Detail_id=bl_Detail.Detail_id) ON
bl_DOIM.DOIM_id=bl_Bill.DOIM_id);

Bill_Two Query:
SELECT bl_Category.Category_id, bl_Category.Category_cd,
bl_Category.Category_nm, bl_Category.Category_Cost_no, bl_Category.Unit_cd,
bl_Category.Category_Order_id, bl_Category.Service_id, bl_Service.Service_tx,
bl_Service.Service_Order_id, bl_SubCategory.SubCategory_cd,
bl_SubCategory.SubCategory_nm, bl_SubCategory.SubCategory_Cost_no,
bl_SubCategory.Unit_cd, bl_SubCategory.SubCategory_Order_id,
bl_SubCategory.Category_id
FROM (bl_Service INNER JOIN bl_Category ON bl_Service.Service_id =
bl_Category.Service_id) LEFT JOIN bl_SubCategory ON bl_Category.Category_id =
bl_SubCategory.Category_id;

I'd found a comment in this newsgroup that all the joins had to be left
joins, so I changed Query_One to be all left joins.

Any help is appreciated.

 

[Roger Carlson]

For an explanation of the Ambiguous Outer Join error, go here:
http://rogersaccessblog.blogspot.com/2008/11/access-101-why-do-i-get-ambiguous-outer.html

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I'm trying to write a query to gather information for a report. When I
first
did it, I got the message that the SQL statement could not be executed
because it contains ambiguous outer joins. I looked at the example in the
Help, which is a very simplistic example and tried to apply it by
splitting
my query into two queries.

Bill_One Query calls Bill_Two Query:

Bill_One Query:
SELECT bl_Detail.Detail_id, bl_Detail.Bill_id, bl_Detail.Category_id,
bl_Detail.Category_Cost_no, bl_Detail.Qty_no, bl_Detail.chk_Picked,
bl_Detail.chk_Has_SubCat, bl_Detail.chk_Has_Comment,
bl_Detail_SubCategory.Detail_id, bl_Detail_SubCategory.SubCategory_id,
bl_Detail_SubCategory.SubCategory_Cost_no,
bl_Detail_SubCategory.chk_Picked,
bl_Comment.Detail_id, bl_Comment.Comment_tx, bl_Comment.Sort_id,
bl_Comment.Delete_flg, bl_Bill.Cust_id, bl_Bill.Version_tx, bl_Bill.FY_id,
bl_Bill.DOIM_id, bl_Bill.Bill_dt, bl_DOIM.DOIM_From_nm,
bl_DOIM.DOIM_Addr1_tx, bl_DOIM.DOIM_Addr2_tx, bl_DOIM.DOIM_Addr3_tx,
bl_DOIM.DOIM_City_tx, bl_DOIM.DOIM_State_tx, bl_DOIM.DOIM_Zip_cd,
bl_DOIM.DOIM_Fax_no, bl_DOIM.DOIM_POC_nm, bl_DOIM.DOIM_Email_tx,
bl_Customer.Cust_Abbrev_nm, bl_Customer.Cust_nm, bl_Customer.Cust_To_tx,
bl_Customer.Cust_Addr1_tx, bl_Customer.Cust_Addr2_tx,
bl_Customer.Cust_City_tx, bl_Customer.Cust_State_tx,
bl_Customer.Cust_Zip_cd,
bl_Customer.Cust_UIC_cd, bl_Customer.Cust_ISA_cd, [Bill_Two Query]
FROM bl_DOIM LEFT JOIN (bl_Detail_SubCategory LEFT JOIN (bl_Comment LEFT
JOIN (bl_Customer LEFT JOIN (bl_Bill LEFT JOIN (bl_Detail LEFT JOIN
([Bill_Two Query] ON bl_Detail.Category_id = bl_Category.Category_id) ON
bl_Bill.Bill_id=bl_Detail.Bill_id) ON bl_Customer.Cust_id=bl_Bill.Cust_id)
ON
bl_Comment.Detail_id=bl_Detail.Detail_id) ON
bl_Detail_SubCategory.Detail_id=bl_Detail.Detail_id) ON
bl_DOIM.DOIM_id=bl_Bill.DOIM_id);

Bill_Two Query:
SELECT bl_Category.Category_id, bl_Category.Category_cd,
bl_Category.Category_nm, bl_Category.Category_Cost_no,
bl_Category.Unit_cd,
bl_Category.Category_Order_id, bl_Category.Service_id,
bl_Service.Service_tx,
bl_Service.Service_Order_id, bl_SubCategory.SubCategory_cd,
bl_SubCategory.SubCategory_nm, bl_SubCategory.SubCategory_Cost_no,
bl_SubCategory.Unit_cd, bl_SubCategory.SubCategory_Order_id,
bl_SubCategory.Category_id
FROM (bl_Service INNER JOIN bl_Category ON bl_Service.Service_id =
bl_Category.Service_id) LEFT JOIN bl_SubCategory ON
bl_Category.Category_id =
bl_SubCategory.Category_id;

I'd found a comment in this newsgroup that all the joins had to be left
joins, so I changed Query_One to be all left joins.

Any help is appreciated.