How to delete duplicate records and leave one?

[KT]

Is there any one click solution that would do the trick? I would like
to create a button, so the person who maintains the database can
perform clean up work to delete duplicate records which contain same
information in the ID field and the account number field once a week.

Thanks in advance!

KT

[Eric Schittlipz]

"KT" <ka****@rocketmail.com> wrote in message
news:fe***********************@posting.google.com. ..

Is there any one click solution that would do the trick? I would like
to create a button, so the person who maintains the database can
perform clean up work to delete duplicate records which contain same
information in the ID field and the account number field once a week.

Thanks in advance!

KT

There is if you don't care which of the duplicate records get deleted.
However, often with "duplicate records" they are not exact duplicates and so
one record is often the best one to keep. E.g. one of the contact records
has a phone and fax number and one only has a phone number.
Anyway, by the looks of things you might be able to alter your database
design so that these sort of duplicates cannot be entered in the first
place, rather than trying to fish them out once a week.

[Pieter Linden]

Short answer: no.
Longer answer: your database is not designed right. That's what
primary keys are all about. "but I need an autonumber..." yeah yeah
yeah. create an autonumber to base the relationships on, make it
unique, so it can act as a surrogate PK. Then make the PK the
smallest combination of all the fields that guarantee a record's
uniqueness. Jet already does the enforcement of uniqueness for you.
You should use it.

[KT]

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...

Short answer: no.
Longer answer: your database is not designed right. That's what
primary keys are all about. "but I need an autonumber..." yeah yeah
yeah. create an autonumber to base the relationships on, make it
unique, so it can act as a surrogate PK. Then make the PK the
smallest combination of all the fields that guarantee a record's
uniqueness. Jet already does the enforcement of uniqueness for you.
You should use it.

Thanks a lot! I think I got it! So I could use combination of fields
as PK to avoid that from happening at the first place...That table I
am working on is the 'many' table from a one to many relationship,
that's why it sometimes has duplicate IDs.

I was worrying about this issue also because I will import tables
frequently (like twice a month), and tables that I import will contain
records that I will have already imported (archive), so is setting up
PK with combinations of fields gonna take care of that too?

[DenBorg]

ka****@rocketmail.com (KT) wrote:

I was worrying about this issue also because I will import tables
frequently (like twice a month), and tables that I import will contain
records that I will have already imported (archive), so is setting up
PK with combinations of fields gonna take care of that too?

Setting up the PK should at least help. To better answer that
question, perhaps you could expound a little as to what is contained
in this table and the nature of the data in the tables that you will
be importing.

For instance, each time you import and you already have some of the
records from your previous import, could the data for those records
have changed since the last import? If not, then you only need to
import those records which have never been imported before.

Perhaps you are importing multiple copies of the tables from different
sources at the same time (as you would if you were importing data from
5 different Customer tables, one from each of 5 different sales
regions). If each table contained data about mutually exlusive
customers, then you don't have a problem.

However, if each copy could contain data about the same customer, then
you have to figure out which copy has the newest info about that
particular customer. And if each location could independently update
that same customer, then you'd have to figure out what changes each
location did to that customer, and import the accumulated changes.
This would be quite an involved process.