Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Smile Unanswered: UNION Query - Access 2003

    I’m trying to take 2 tables and exclude the data on one from the other. For example, I have a master list Tbl_ReportDetail and I want to exclude those in a different list ARC_List. I believe I can do this with a UNION query but my query pulls in both fields. Can you tell me where I might be going wrong?

    SELECT Tbl_ReportDetail.SoldId, ARC_List.CustomerNumber
    FROM Tbl_ReportDetail LEFT JOIN ARC_List ON Tbl_Detail.SoldId = ARC_List.CustomerNumber
    UNION
    SELECT Tbl_ReportDetail.SoldId, ARC_List.CustomerNumber
    FROM Tbl_ReportDetail RIGHT JOIN ARC_List ON Tbl_Detail.SoldId = ARC_List.CustomerNumber
    WHERE ARC_List.CustomerNumber IS NULL;

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    UNION is a set operator, and returns a result set that contains all the distinct rows in the two result sets. The ISO SQL set operator you want is called MINUS, but Access does not support this.
    Nay mind - there's an alternative:
    Code:
     
    SELECT Tbl_ReportDetail.SoldId
    FROM Tbl_ReportDetail 
    WHERE EXISTS (SELECT * FROM ARC_List WHERE Tbl_Detail.SoldId = ARC_List.CustomerNumber)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56
    Thanks. I'll give it a try.
    Kasper

Posting Permissions

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