Remove Duplicates?

[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!

 

[default105]

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

 

[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

 

[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

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!

--

 

[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]...

 

[Pieter Wijnen]

several ways

http://support.microsoft.com/default.aspx?scid=kb;en-us;139444

is one way -> google search: remove duplicates sql
will also provide other ways

Pieter

 

[Pieter Wijnen]

several ways

http://support.microsoft.com/default.aspx?scid=kb;en-us;139444

is one way -> google search: remove duplicates sql
will also provide other ways

Pieter

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

--