Deleting multiple duplicates

C

CindyG

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
 
N

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
 
C

CindyG

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
 
N

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
 
Top