Need a query to get rid of duplicates using two tables



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!?!

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]
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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question