Remove Duplicates?

  • Thread starter Thread starter dan
  • Start date Start date
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!
 
Sure you could but why is your table field not set up to not allow dulicates
in this field
 
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
 
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...



--
 
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]...
 
Back
Top