Results 1 to 2 of 2
  1. #1
    Danny2024 is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2018
    Posts
    9

    Show value not in both query's

    Hi



    Been a while since I used access but recently got back into it

    I am using Access 2016 and I want to look at a table and a query and show what parts do not appear in both

    Tbl_QP_Partnumbers - Shows all the partnumbers that need a document creating
    Qry_Insp_Plan_Created_Data - Shows all the partnumbers that have had a document created

    I want a simple query that shows what partnumbers are still on Tbl_QP_Partnumbers and as such need the document required creating still

    The below shows what is on both lists but how do I alter this to get what I want?

    SELECT Tbl_QP_Partnumbers.Partnumber
    FROM Tbl_QP_Partnumbers INNER JOIN Qry_Insp_Plan_Created_Data ON Tbl_QP_Partnumbers.Partnumber = Qry_Insp_Plan_Created_Data.Partnumber;

    Thanks in advance

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Welcome to the forum

    Change to an OUTER JOIN and use Is Null as shown below - its called an unmatched query - there is a wizard for doing this

    This query gives the unmatched partnumbers in the table Tbl_QP_Partnumbers:

    Code:
    SELECT Tbl_QP_Partnumbers.Partnumber
    FROM Tbl_QP_Partnumbers LEFT JOIN Qry_Insp_Plan_Created_Data ON Tbl_QP_Partnumbers.Partnumber = Qry_Insp_Plan_Created_Data.Partnumber
    WHERE Qry_Insp_Plan_Created_Data.Partnumber Is Null;
    This one gives the unmatched partnumbers in the query Qry_Insp_Plan_Created_Data:

    Code:
    SELECT Qry_Insp_Plan_Created_Data.Partnumber
    FROM Tbl_QP_Partnumbers RIGHT JOIN Qry_Insp_Plan_Created_Data ON Tbl_QP_Partnumbers.Partnumber = Qry_Insp_Plan_Created_Data.Partnumber
    WHERE Tbl_QP_Partnumbers.Partnumber Is Null;
    And to get both of these in the same output use a UNION query based on both of those.
    You can't do this using the query design window; change to SQL view and paste in the code

    Code:
    SELECT Tbl_QP_Partnumbers.Partnumber
    FROM Tbl_QP_Partnumbers LEFT JOIN Qry_Insp_Plan_Created_Data ON Tbl_QP_Partnumbers.Partnumber = Qry_Insp_Plan_Created_Data.Partnumber
    WHERE Qry_Insp_Plan_Created_Data.Partnumber Is Null;
    UNION SELECT Qry_Insp_Plan_Created_Data.Partnumber
    FROM Tbl_QP_Partnumbers RIGHT JOIN Qry_Insp_Plan_Created_Data ON Tbl_QP_Partnumbers.Partnumber = Qry_Insp_Plan_Created_Data.Partnumber
    WHERE Tbl_QP_Partnumbers.Partnumber Is Null;
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 18
    Last Post: 03-13-2017, 12:32 PM
  2. Replies: 3
    Last Post: 07-20-2014, 08:56 PM
  3. Replies: 4
    Last Post: 04-03-2014, 08:03 AM
  4. Replies: 3
    Last Post: 03-07-2013, 10:44 AM
  5. Replies: 3
    Last Post: 01-15-2013, 01:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums