Need a query to get rid of duplicates using two tables

[Susan]

I have 2 tables - one -A - has 100 records - the other - B - has 150
records - they have a common field NAME - table B has the same 100 records as
table A, plus 50 more - I want to create a table with only the 50 records
that are not unique to both tables. The tables I'm dealing with have much
more than 100 records so I need a query to take out the common records and
leave only the unique 50 records from table B. This is not the typical
"duplicate records" query as it relates to two tables. Help!?!
Susan

 

[John Spencer]

This sounds like you want to use the unmatched query wizard which will build a
query to find all records in one table that do not have a matching value in
another table.

The SQL of the query that will be built will look something like:

SELECT TableB.[Name]
FROM TableB LEFT JOIN TableA
ON TableB.[Name] = TableA.[Name]
WHERE TableA.[Name] is null

By the way, Name is a poor choice for a field name or table name or any other
object in Access. Everything has a NAME property and it is possible for
confusion to arise. I would suggest you use something like ClientName,
PersonName, TheName, ItemName, etc. to preclude any possible problems.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County