LEFT JOINS in MS Access

Question

I am trying to resolve another MS Access request and continue to receive an invalid operation error. Help doesn't seem to apply, as I am just running a query. Everything works like INNER JOINS , but when I get back to the LEFT JOIN error.

---

 SELECT * FROM ((((orders INNER JOIN orders_customers ON orders.CUST_ORDER_ID = orders_customers.ID) LEFT JOIN quoted_theory ON orders.PART_ID = quoted_theory.PART_ID) LEFT JOIN conversions ON orders.PART_ID = conversions.PART_ID) LEFT JOIN dbo_WO_Header ON orders.CUST_ORDER_ID = dbo_WO_Header.PPC_Number) INNER JOIN lines_qry ON orders.CUST_ORDER_ID = lines_qry.WORKORDER_BASE_ID 

---

I can get one level of LEFT JOIN , but every time I add a second LEFT JOIN , a tooltip appears.

Answer 1

Access to the db engine is often interrupted by mixing INNER and LEFT inputs. If this request works without the last internal connection ...

 SELECT * FROM (((orders INNER JOIN orders_customers ON orders.CUST_ORDER_ID = orders_customers.ID) LEFT JOIN quoted_theory ON orders.PART_ID = quoted_theory.PART_ID) LEFT JOIN conversions ON orders.PART_ID = conversions.PART_ID) LEFT JOIN dbo_WO_Header ON orders.CUST_ORDER_ID = dbo_WO_Header.PPC_Number 

... then you can try this part as a subquery and inner lines_qry for the subquery. This may result in an error.

 SELECT * FROM ( SELECT * FROM (((orders INNER JOIN orders_customers ON orders.CUST_ORDER_ID = orders_customers.ID) LEFT JOIN quoted_theory ON orders.PART_ID = quoted_theory.PART_ID) LEFT JOIN conversions ON orders.PART_ID = conversions.PART_ID) LEFT JOIN dbo_WO_Header ON orders.CUST_ORDER_ID = dbo_WO_Header.PPC_Number ) AS sub INNER JOIN lines_qry ON sub.CUST_ORDER_ID = lines_qry.WORKORDER_BASE_ID 

If any table other than orders includes a field named CUST_ORDER_ID , you need something other than SELECT * in the subquery to avoid ambiguity.

Answer 2

 SELECT * FROM ( SELECT * FROM (((orders INNER JOIN orders_customers ON orders.CUST_ORDER_ID = orders_customers.ID) LEFT JOIN quoted_theory ON orders.PART_ID = quoted_theory.PART_ID) LEFT JOIN conversions ON orders.PART_ID = conversions.PART_ID) LEFT JOIN dbo_WO_Header ON orders.CUST_ORDER_ID = dbo_WO_Header.PPC_Number ) AS sub INNER JOIN lines_qry ON sub.CUST_ORDER_ID = lines_qry.WORKORDER_BASE_ID