Del / Mark Duplicates??

[sbcglobal]

I have some VB like below, to delete duplicate. The Access table is 'RED',
key index field is 'Index', the VB nevigate through all records in 'Index'
and delete duplicate if found.

There are 2 problems:

1. I have to sort key field 'Index' first before del, since the sub only
compare adjacent 2 recordsets. Is there any other way to do this? Or how
would I sort records in VB?

2. Instead deleting, I'd like to name another field called 'Duplicate'
(yes/no). Can I modify below VB to mark field 'Duplicate' to 'yes' or 'no',
to indicate this is duplicate record (or not)?

Note if there are 5 recordsets in 'Index' have value=12135, then I ONLY need
to mark the first one (in field 'Duplicate') 'No', and rest 4 records 'Yes'.

Many thanks,

**************************************************
The Spoon Does Not Exist.
+++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub del_duplicate()
On Error Resume Next

Dim db As Database
Dim rst As Recordset
Dim strDupName As String, strSaveName As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("RED")

If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else
rst.MoveFirst
Do Until rst.EOF
strDupName = rst.Fields("Index")
If strDupName = strSaveName Then
rst.Delete
Else
strSaveName = rst.Fields("Index")
End If
rst.MoveNext
Loop

Set rst = Nothing
Set db = Nothing

End If

End Sub

..

 

[Allen Browne]

Please do not post the same message to many different groups.

 

[Rick]

Try something like this:
(check out the SQL view of a query-it's a great tool)
(use ORDER BY to sort records)

Dim db As Database

Dim rst As Recordset
Dim strDupName As String, strSaveName As String

Dim strSQL as string

Set db = CurrentDb()

strSQL = "SELECT RED.Index, RED.Duplicate "
strSQL = strSQL & "FROM RED "
strSQL = strSQL & "ORDER BY RED.Index;"
Set rst = db.OpenRecordset(strSQL)

If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else
rst.MoveFirst
Do Until rst.EOF
strDupName = rst!Index
If strDupName = strSaveName Then
rst.edit

rst!Duplicate = "Yes"
rst.update

Else
rst.edit

rst!Duplicate = "No"
rst.update

 

[Bas Cost Budde]

(check out the SQL view of a query-it's a great tool)

Speaking of tools: have you tried my NavQueries?