Show <> Records Between 2 Tables ?? (ANTI-Join)

[kev100 via AccessMonster.com]

Folks,

I've got 2 tables.

One has 2000 records (original)

The Second has 1000 (created from the Original with 1000 records removed).

There is a unique ID (which is in the Original and the Second for matching
like records...the CustomerNum).

I'm familiar with creating a query that would join them at the CustomerNum....
which would generate a list of records with matching CusterNums in each table)

 

[Roger Carlson]

In the New Query dialog box, there's a wizard to create an Unmatched Query.
That's what you're looking for. One of the tables has to be the "base"
table, so you might have to create two unmatched queries using a different
table as the base table in each one.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Spencer]

There is an unmatched query wizard which will let you identify records in
one table that are not in the other table.

It will build a query that would look something like
SELECT TableA.*
FROM TableA LEFT JOIN TableB
ON TableA.CustomerNum = TableB.CustomerNum
WHERE TableB.CustomerNum is Null

In Design view
-- Add both tables to the query
-- Join the tables on CustomerNum
-- Double click on the Join line
-- Select All records in TableA and only those from TableB that match
-- Add all the fields in TableA that you want to see
-- Add CustomerNum from TableB
-- Set the criteria for TableB.CustomerNum to Is Null
-- Run the query

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..