Groups > Databases > Access Databases and AccessDataSource Control > Re: Complex Left Outer JOINs with VS and Access




Complex Left Outer JOINs with VS and Access

Complex Left Outer JOINs with VS and Access
Wed, 26 Mar 2008 18:53:36 +000
I have a JOIN spanning 4 tables, which works - all are Inner JOINs.  However, if
I use the wizard to make one a LEFT OUTER, then when I use the wizard to
complete it I receive an error "Generated SELECT statement.  Join
expression not supported."  I understand that the logic must contain some
ambiguity, but I don't know how to clean it up.  

My primary table is the tblWorkOrder table and it should join tblPOLineItems
with a Left Outer join.  There is one additional join on each side of this Left
Outer -- tblWorkOrder joining with tblProducts and tblPOLineItems joining with
tblPurchaseOrders.  Those are both fine using Inner joins.  I need to be able to
display those WorkOrders that have not been assigned to POLineItems.

Any help would be appreciated.

Here is the query as the wizard created it...

SELECT     tblWorkOrder.WorkOrderNumber, tblWorkOrder.POLineItemID,
tblWorkOrder.WorkOrderStatus, tblWorkOrder.ActualStartDate, 
                      tblWorkOrder.ActualCompletionDate,
tblWorkOrder.ReceivedDate, tblWorkOrder.AssignedTo, tblWorkOrder.DateAssigned,
tblWorkOrder.AcceptedBy, 
                      tblWorkOrder.DateAccepted, tblWorkOrder.DelegatedTo,
tblWorkOrder.Quantity, tblWorkOrder.UnitPrice, tblWorkOrder.TotalPrice,
tblWorkOrder.Due, 
                      tblWorkOrder.ProductID, tblWorkOrder.EnteredDate,
tblWorkOrder.EnteredBy, tblPOLineItems.PurchaseOrderLineNumber, 
                      tblPOLineItems.PurchaseOrderID,
tblPurchaseOrders.PONumber, tblProducts.ProductDescription, tblWorkOrder.Refurb
FROM         ((tblProducts INNER JOIN
                      tblWorkOrder ON tblProducts.ProductID =
tblWorkOrder.ProductID) LEFT OUTER JOIN
                      (tblPurchaseOrders INNER JOIN
                      tblPOLineItems ON tblPurchaseOrders.PurchaseOrderID =
tblPOLineItems.PurchaseOrderID) ON 
                      tblWorkOrder.POLineItemID = tblPOLineItems.POLineItemID)
WHERE     (tblWorkOrder.WorkOrderNumber = ?)
Post Reply
Re: Complex Left Outer JOINs with VS and Access
Wed, 26 Mar 2008 23:40:08 +000
Not sure I got it correctly (it's hard to write SQL without being able to try it
out), but to me it seems you cannot do without replacing one of the INNER JOINs
with an OUTER JOIN, as you have two JOINs that rely on tblPOLineItems, which is
ultimately outer joined. So my suggestion is: 


FROM  tblWorkOrder
      INNERJOIN tblProducts ON tblProducts.ProductID = tblWorkOrder.ProductID
      LEFTOUTER JOIN tblPOLineItems ON tblWorkOrder.POLineItemID =
tblPOLineItems.POLineItemID 
      LEFTOUTER JOIN tblPurchaseOrders ON tblPurchaseOrders.PurchaseOrderID =
tblPOLineItems.PurchaseOrderID
Post Reply
Re: Complex Left Outer JOINs with VS and Access
Thu, 27 Mar 2008 03:16:54 +000
Thank you very much!  It works perfectly!

I am curious why this matters, though.  Thanks again!
Post Reply
about | contact