Find Duplicates between 2 tables

  • Thread starter Thread starter Jean
  • Start date Start date
J

Jean

I am working with an Access 2000 database. I import a text
file into a table, add some calculated fields to the
records, then append the records to a different table.
After the records are imported, I delete all records from
the original table.

My problem is I want to delete the records from the
original table that already exist in the destination table
before I append them.

The fields that I need to compare are:
Type (1-4)
ChkNum (number)
OnPad (date & time)
OffPad (date & time)
Start (date & time)
End (date & time)
Total (imported as text, converted to Currency during
append)
StrID (number)

The Type, ChkNum, Total, and StrID are the only fields
that are consistently populated.

If I create a query that joins all of the fields listed
above, I only get the duplicate records that have all
fields populated.

Any suggestions?

Thank you,
Jean
 
In the query tab of the Database window, click the New button and choose the
Find Duplicates option. This should get you started. You will probably have
to massage it a bit because it isn't a dupe if not all the fields match.

Next, you said that you wanted to delete records before appending. If you
are going to do this, you will probably want to call the queries from code
and wrap them in a Transaction so that if the append fails, you can undo the
delete. Otherwise, do the append first then delete the dupes. The 2nd way
may be what you're doing, but the way you worded it I wasn't sure (how do
they already exist if you haven't appended them yet?).
 
Thank you for the response.

The 'Find Duplicates' wizard only allows you to find dups
in one table as far as I can tell. I want to find the
records in one table that are already in another table
before I append them to that table.

The destination table contains all records that have
previously been imported. Therefore I want to catch
records that have already been imported before I append
the newest imported records to the destination table.

I am performing other processes on the imported data
before I append it to the destination table so I need to
do the deletion at a specific step in the process before
the append.

Jean
 
For duplicates in 2 different tables, if there is a unique ID field in both of the tables
that is also duplicated you could just link the 2 tables on that field. The only value
that will be returned are those where both tables match.
 
There is no unique ID field that is duplicated in both
tables. The fields that I previously listed are the only
fields that will be duplicates and none of them are unique
ID fields. Although each table does have a unique ID
field, they are not duplicates of each other.

Jean
-----Original Message-----
For duplicates in 2 different tables, if there is a
Click to expand...
unique ID field in both of the tables
 
Then the records aren't "true duplicates" because they aren't the same all the way across.
You will need some sort of unique identifier to know whether or not they are duplicated or
are supposed to be another copy of one that is already there.

If this doesn't matter, then you can still use the query and draw links between the tables
on each field you are trying to compare. I just tried it and it works, except Null
apparently doesn't equal Null, so if you have a Null value in any of the fields that
record will be skipped.
 
As I stated in my original posting, I know the query works
if all fields are populated but I need to get all
duplicate records which includes records with Null fields
in both records (the import table and the destination
table).

Jean
-----Original Message-----
Then the records aren't "true duplicates" because they
Click to expand...
aren't the same all the way across.
You will need some sort of unique identifier to know
Click to expand...
whether or not they are duplicated or
are supposed to be another copy of one that is already there.

If this doesn't matter, then you can still use the query
Click to expand...
and draw links between the tables
on each field you are trying to compare. I just tried it and it works, except Null
apparently doesn't equal Null, so if you have a Null
Click to expand...
value in any of the fields that
 
You could run an update query on both tables to put some otherwise unused data in the Null
fields, just to populate them then run the query to check for dupes. When you are done,
you could run another update query to remove the bogus data and change it back to Null.
Just pick some value that will match the data type for the field and doesn't appear
anywhere in any of the records. This should work as long as none of the fields have only 2
values possible, such as a Yes/No field.
 
Thanks. I hadn't thought of that. It sounds like it might
work. I'll give it a try and let you know.

Jean
-----Original Message-----
You could run an update query on both tables to put some
Click to expand...
otherwise unused data in the Null
fields, just to populate them then run the query to check for dupes. When you are done,
you could run another update query to remove the bogus
Click to expand...
data and change it back to Null.
Just pick some value that will match the data type for the field and doesn't appear
anywhere in any of the records. This should work as long
Click to expand...
as none of the fields have only 2
 
That worked.

Thanks,
Jean
-----Original Message-----
Thanks. I hadn't thought of that. It sounds like it might
work. I'll give it a try and let you know.

Jean

otherwise unused data in the Null check
for dupes. When you are done,
data and change it back to Null.
as none of the fields have only 2
.
Click to expand...
 
Back
Top