finding non-duplicated records

[Confused about Access]

Help! I have two tables, one is a "master list" and the other table has most
of the files that is in the master list. Is there anyway to extract those
files that are duplicated in both the master list and the other list while
leaving the other files remaining? If anyone could help me, I would REALLY
appreciate it. Thanks!!!

 

[Klatuu]

Use the Find Duplicates query wizard. It will help you build a query that
finds the duplicate records (not files) in the two tables. Then you can
modify that into a delete query to delete the records from one of the two
tables. After, of course, you make a backup copy of you data.

 

[Confused about Access]

Thank you for replying, but I don't understand how this would work. I am
using Access 2003, and I tried using the Find Duplicates query wizard, but it
says it can only find duplicates in one table, and not both. Additionally, I
have some duplicates within the table that I do not wish to disrupt, only
those records that are present in BOTH tables. Can I still do this in 2003
and the Find Duplicates query wizard? Thanks!!

 

[Klatuu]

Doh!
Sorry, I wasn't thinking clearly when I responded. The way to find the
duplicates between two tables is to create a query that has a WHERE clause
that is a subquery that looks for a match in the other table. So you need to
use the field name or names that would determine if there are duplicates. It
would be something like:

SELECT * FROM MainTable WHERE KeyFieldName IN(SELECT OtherKeyField FROM
OtherTable);

 

[Confused about Access]

That sounds great. I have one other question. Where do I enter " SELECT *
FROM MainTable WHERE KeyFieldName IN(SELECT OtherKeyField FROM

 

[Klatuu]

Go into Design view. When you get the Select Table dialog, close it. Then
in the upper left corner of your screen will be a button SQL. Click that and
the designer will change to a blank screen. Put the code in there. Now, of
course, you will have to change the table and field names to use your actual
names.

 

[Confused about Access]

Thank you for your help. Actually what also worked was "Find unmatched query
wizard." Thanks for all of your help!! It was greatly appreciated!

 

[John Spencer]

IF you want to return the records in Master List that are not in the other
table (part one)AND the records in the other table that are not in the Master
List (Part two).

You can use the unmatched query wizard to do this for part one and again for
part two. Then you should be able to use a UNION query of the two queries to
get the result.

If I've misunderstood what you want post back.

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

 

[Confused about Access]

This is great, thanks.

IF you want to return the records in Master List that are not in the other
table (part one)AND the records in the other table that are not in the Master
List (Part two).

You can use the unmatched query wizard to do this for part one and again for
part two. Then you should be able to use a UNION query of the two queries to
get the result.

If I've misunderstood what you want post back.

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