MS-Access finding unmatched records

[BennyCriziko]

I have two tables in MS-Access "tmpChanges" (685 records) and "tmpChangesWithDates" (785 records). I want to design a query that will only show those 100 records that are the difference between the two tables. Each has 4 fields and are common to both tables: State, City, Sale, SaleDate. The table "tmpChanges" has no value listed in the SaleDate field. I tried this and it did not work, any clues?: SELECT tmpChangesWithDates.City, tmpChangesWithDates.State, tmpChangesWithDates.Sale, tmpChangesWithDates.SaleDate FROM tmpChangesWithDates LEFT JOIN tmpChanges ON tmpChangesWithDates.[Sale] = tmpChanges.[Sale] WHERE (((tmpChanges.SaleDate) Is Null));

[Lost User]

BennyCriziko wrote:

any clues

You got the wrong join if you're looking for differences. I'd do it with a "WHERE NOT IN" and a subquery.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

[BennyCriziko]

Like this?: SELECT tmpChangesWithDates.City, tmpChangesWithDates.State, tmpChangesWithDates.Sale, tmpChangesWithDates.SaleDate FROM tmpChangesWithDates WHERE NOT IN (SELECT tmpChangesWithDates.City, tmpChangesWithDates.State, tmpChangesWithDates.Sale, tmpChangesWithDates.SaleDate FROM tmpChangesWithDates LEFT JOIN tmpChanges ON tmpChangesWithDates.[Sale] = tmpChanges.[Sale] WHERE (((tmpChanges.SaleDate) Is Null)));

[Chris Quinn]

SELECT tmpChangesWithDates.City, tmpChangesWithDates.State, tmpChangesWithDates.Sale, tmpChangesWithDates.SaleDate FROM tmpChangesWithDates LEFT OUTER JOIN tmpChanges ON tmpChangesWithDates.[Sale] = tmpChanges.[Sale] WHERE (((tmpChanges.SaleDate) Is Null));

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================

[Lost User]

If the result is correct, then yes :-\

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]