Deleting Duplicates

G

Gina

I can get a list of dups. I want to delete just the actual duplicate (not
the original). If I have 6 records that match, I want to delete 5 of them
only.

Please, How can I do this?

Thank You.
 
L

lgalumbres

Hi,

Not sure what you mean by " I want to delete just the actual duplicate
(not
the original)." ? If they are duplicate records meaning a row is
repeated multiple times then there is no distinction to which one is
the original record.

But in any case, you can use the DISTINCT keyword to get each distinct
record in your table and insert the results into another table. The
result would give you a copy of your table without the duplicates.
Something along these lines....

SELECT DISTINCT Column1, Column2
INTO TableNoDups
FROM TableWithDups
GROUP BY Column1, Column2

Cheers!
- Lem
 
G

Gina

That works perfectly, thank you.
--
Gina


[email protected] said:
Hi,

Not sure what you mean by " I want to delete just the actual duplicate
(not
the original)." ? If they are duplicate records meaning a row is
repeated multiple times then there is no distinction to which one is
the original record.

But in any case, you can use the DISTINCT keyword to get each distinct
record in your table and insert the results into another table. The
result would give you a copy of your table without the duplicates.
Something along these lines....

SELECT DISTINCT Column1, Column2
INTO TableNoDups
FROM TableWithDups
GROUP BY Column1, Column2

Cheers!
- Lem
Click to expand...
 
C

cynteeuh

In the example below, where would you put the "Select Distinct" info -- query?
 
D

DimmickT

I am importing records from another database, there are duplicate records but
the only was to identify them is with the First and Last Name fields. I have
a duplicate query that identifies all of the duplicates but now I want to
write a delete query that will delete only the duplicates from the table.
How can I do this?
 
D

DimmickT

I was not able to get that to work plugging the info in. I was abe to get
the SELECT DISTINCT to work but would like to pull additional fields into the
no dup table like birthdate, action, volume etc... Where do I add those
fields in the below SQL statement:

SELECT DISTINCT Tbl_New.FirstName, Tbl_New.LastName
INTO Tbl_No_Dups
FROM Tbl_New
GROUP BY Tbl_New.FirstName, Tbl_New.LastName;
 
R

Roger Carlson

The solution I referenced doesn't have a Select Distinct in it at all. It
uses a Delete Query. However, it *does* requires a single, unique key. It
won't work with a compound key.

In your instance, however, there is a simpler solution. Since you are
importing data, you can create a Unique Index on your two fields and then
just import your records. Only the first of a duplicate record will be
imported and the rest will be discarded.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
R

Roger Carlson

HOW TO CREATE A UNIQUE INDEX:
Creating a unique index on a single field is easy. You can do it in the
table design view. At the bottom portion of the screen is a property called
Index. Set that to Yes(No Duplicates). If you want to allow Nulls, set
Ignore Nulls to Yes.

Creating a unique index on multiple fields is not so straight-forward. In
the table Design View, click the Indexes Button (it has a lightening bolt on
it). Under Index Name, type a meaningful name and then select a field in
the Field Name column. In the lower portion, set the Unique property to
Yes. Now WITHOUT entering another Index Name, select your second field in
the next Field name and so on until you have all the fields you need:

IndexName FieldName SortOrder
========= ========= =========
WholeName LastName
FirstName

For performance reasons, you should create them in the order that they
appear in the table in design view.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Top