Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2016
    Posts
    25

    Unanswered: Creating a Join Query

    I am trying to create a join query that will combine 3 different queries called Q_OrderDelAlt, Q_OrderDtl, and Q_OrderHdr.

    The 3 different tables I want to use are called: OrderDetail, OrderHeader and OrderDeliveryAlt.

    Each table has the same field called OrderNu

    Here is the SQL for the join query:

    SELECT OrderDetail.OrderNu,
    FROM OrderDetail
    WHERE (((OrderDetail.OrderNu)=[Enter the OrderNu:]))
    ORDER BY OrderDetail.OrderNu
    Union
    SELECT OrderHeader.OrderNu,
    FROM OrderHeader
    WHERE (((OrderHeader.OrderNu)=[Enter the OrderNu:]))
    ORDER BY OrderHeader.OrderNu
    UNION SELECT OrderDeliveryAlt.OrderNu,
    FROM OrderDeliveryAlt
    WHERE (((OrderDeliveryAlt.OrderNu)=[Enter the OrderNu:]))
    ORDER BY OrderDeliveryAlt.OrderNu;

    I get this error message when I run the Join query:

    Click image for larger version. 

Name:	Join Query - Error Message.jpg 
Views:	4 
Size:	23.9 KB 
ID:	17381

    Does anybody know how to make this join query work properly?

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    In Access union queries, you can only have one ORDER BY clause. IIRC, it should be before the first UNION statement.

    As written, your query will prompt for the order number three times - is that what you want?
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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