• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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

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?

  • 2
1 Solution
Rey Obrero (Capricorn1)Commented:
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));
Rey Obrero (Capricorn1)Commented:
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));
BevosAuthor Commented:
Thanks so much Capricorn1, this works exaclty as intended.


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now