Deleting multiple duplicates

[Guest]

Hi:

I have been struggling with the following problem. I hope that someone will
know an easy way to do this.

I want to be able to delete multiple duplicate rows which have the same ID
number but does not entirely match all information in the row, so therefore
would not be an actual "duplicate". For example

John Smith 5/10/05
John Smith 6/10/05
John Smith 7/10/05
John Smith 10/10/05

I want to delete the last 3 records for John Smith. Does anyone know of an
easy way to do this?

Thanks

Cindy

 

[Nikos Yannacopoulos]

Cindy,

The simplest way I can think of to do this is to use a Make-Table query
which puts the desired records only in a new table, run it, then rename
the old table to something else, and the new one to the original name;
when happy it's all fine, you can delete the old table altogether. In
the example SQL below, I'm using the following table and field name
assumnptions:
Exiting table: tblOldTable, fields fldName and fldDate
New table: tblNewTable

SELECT tblOldTable.fldName, Min(tblOldTable.fldDate) AS MinOffldTable
INTO tblNewTable
FROM tblOldTable
GROUP BY tblOldTable.fldName

Just change my assumed names to the actual ones.

HTH,
Nikos

 

[Guest]

Nikos,

Thanks for your timely response. I am not sure if I understand what to do.
I have 50,000 rows and don't want to have to delete the duplicates manually.
What is your statement saying?

Thanks

Cindy

 

[Mike Painter]

Hi:

I have been struggling with the following problem. I hope that
someone will know an easy way to do this.

I want to be able to delete multiple duplicate rows which have the
same ID number but does not entirely match all information in the
row, so therefore would not be an actual "duplicate". For example

John Smith 5/10/05
John Smith 6/10/05
John Smith 7/10/05
John Smith 10/10/05

I want to delete the last 3 records for John Smith. Does anyone know
of an easy way to do this?

You may have to do that in code. Almost definitely if the "wrong" ones are
not the first record access comes to.

You can create an empty copy of the table with Ctrl-C, Ctrl-V then make the
"John Smith" field indexed with no duplicates allowed.

An append query from old to new will get rid of all but the first dupe.
I think this is as the records are entered, not as they are sorted.

You may have to split the table and work with that, trying to find something
that will let you automate the elimination of wrong data.

Frequently it can only be done by humans who knows what's right.

What do you mean when you say "does not entirely match..."?

 

[Nikos Yannacopoulos]

Cindy,

My suggestion is basically somewhat different to deleting unwanted
records, it is actually picking up and keeping the wanted ones in a new
table, then discarding the original table.
To do that, you need a query that wil pick up one reocrd for each name,
specifically the one with the earliest date (as per your example). This
can be easily accomplished with the use of a Totals query, grouping on
name, selecting Min of dates; additionally, this query can be a make
table one, which puts the selected records in a new table. That is
exactly what the query I gave you does. Start making a new query in
design view, add no table, revert to SQL view and paste the SQL
expression I gave you; change the existing table and field names to the
actual ones; revert to datasheet view, to see if the query returns what
you expect. When happy with the query results, run the query and the new
table is made. Then rename the old one to a different name, and rename
the new one to the old one's original name, and the job is done. When
happy with the new table, you can delete the old one altogether.

A general suggestion: whenever trying out something you are not very
comfortable with, always take a back-up copy first!

HTH,
Nikos