Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Are two left joins possible?

    Hi - thanks for reading........

    The back story is - I have a project where I determine if an order is opened (back ordered) or closed (billed complete) after invoices.
    The order identifiers are OID (order) OLID (order line items)
    I do this in my database by joining the invoices table to the orders table (qryOrders) and comparing the dollar values (the order lines extended are worth x amount minus the invoice amount). If it is 0, then it is billed complete and goes to one query qryBC. If it is not, it goes to a make table so that each order line can be manually updated with SHIPQTY, appLineUpdates.

    I then take the appLineUpdates and subtract from order lines (order line qty - SHIPQTY) to get balance. The below qry works perfect for that:

    Code:
    SELECT one.*, two.SHIPQTY
    FROM qryOrders AS one LEFT JOIN appLineUpdates AS two ON (one.OID = two.OID) AND (one.OLID = two.OLID);
    What I want to do is also have this qry determine the billed complete, so I would like to add another left join for qryBC.

    I'm not sure how to write that in as a second left join.......I tested it in it's own qry like this works perfect:
    Code:
    SELECT one.*, two.SHIPQTY
    FROM qryOrders AS one LEFT JOIN qryBC AS two ON (one.OID = two.OID) AND (one.OLID = two.OLID);
    thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Usually, Access will force you to use 2 queries for this king of operation. Build a first query with a left join, then build a second query also with a left join to the fist query.
    Have a nice day!

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    have a go with:
    Code:
    AND ON (one.OLID = two.OLID)
    i'm sitting on a mac at the moment so i can't check: maybe it works.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •