Access 2007: SQL question, compare two fields and report which values are not repeated

Posted on 2011-04-26
Last Modified: 2012-05-11
Hello, I would like to make a simple query in SQL that shows me which values for a field are not contained within both fields.  I have a table (tblRecordNumbers) with RecordNumberOld and RecordNumberNew fields.  Record number old has 675 entries and RecordNumberNew has 941.  I need to know what numbers in RecordNumberNew are not in record number old. Can anyone please explain how to do this?

Question by:Bevos
    LVL 119

    Expert Comment

    by:Rey Obrero
    do a select query using a left join using the same table  

    SELECT tblRecordNumbers.RecordNumberOld, tblRecordNumbers_1.RecordNumberNew
    FROM tblRecordNumbers LEFT JOIN tblRecordNumbers AS tblRecordNumbers_1 ON tblRecordNumbers.RecordNumberOld = tblRecordNumbers_1.RecordNumberNew
    WHERE (((tblRecordNumbers_1.RecordNumberNew) Is Null));
    LVL 119

    Accepted Solution

    or you can do the reverse

    SELECT tblRecordNumbers.RecordNumberNew, tblRecordNumbers_1.RecordNumberOld
    FROM tblRecordNumbers LEFT JOIN tblRecordNumbers AS tblRecordNumbers_1 ON tblRecordNumbers.RecordNumberNew = tblRecordNumbers_1.RecordNumberOld
    WHERE (((tblRecordNumbers_1.RecordNumberOld) Is Null));

    Author Comment

    Thanks so much Capricorn1, this works exaclty as intended.


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now