Remove Duplicates?

D

dan

Hi,

I have a table similiar to this:

ColA Col B
100 Dog
101 Cat
101 Horse
102 ouse


When there is a duplicate in ColA, I want to delete the entire row of
one of the duplicates and just keep one. Sometimes there might be three
or four of these, and I just want one. How can i do this? SQL Statement
or something else?

Thank you!
 
G

Guest

Sure you could but why is your table field not set up to not allow dulicates
in this field
 
P

Pieter Wijnen

I've had a zillon of those

the simlpest way (if you don't rate the remainder)

Sub KillDups()
Dim Db As DAO.Database
Dim Rs AS DAO.Recordset
Dim LastKey as string ' in the ex

Set db = Access.CurrentDb
Set Rs = Db.OpenRecordset("SELECT DUPKEY FROM MYTABLE",DAO.dbOpenDynsaset)
While Not Rs.EOF
If Rs.Fields(0).Value = LastKey Then
Rs.Delete
Else
LastKey = Rs.Fields(0).Value
End If
Rs.Movenext
Wend

Pieter
 
P

Pieter Wijnen

I've had a zillon of those

the simlpest way (if you don't rate the remainder)

Sub KillDups()
Dim Db As DAO.Database
Dim Rs AS DAO.Recordset
Dim LastKey as string ' in the ex

Set db = Access.CurrentDb
Set Rs = Db.OpenRecordset("SELECT DUPKEY FROM MYTABLE",DAO.dbOpenDynsaset)
While Not Rs.EOF
If Rs.Fields(0).Value = LastKey Then
Rs.Delete
Else
LastKey = Rs.Fields(0).Value
End If
Rs.Movenext
Wend

Pieter


dan said:
Hi,

I have a table similiar to this:

ColA Col B
100 Dog
101 Cat
101 Horse
102 ouse


When there is a duplicate in ColA, I want to delete the entire row of
one of the duplicates and just keep one. Sometimes there might be three
or four of these, and I just want one. How can i do this? SQL Statement
or something else?

Thank you!
Click to expand...



--
 
A

Angus Comber

Is there not an SQL statement which can do this?

Angus

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace
..with.norway> wrote in message news:[email protected]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Hide duplicates 4
Countif Help 8
Slow Application 1
Tricky Date Calculation 2
Average depending on criteria 4
Something like Countif in Access? 5
select multiple items in list to run query 1
Delete Duplicate data row wise from each cell 2

Top