Updating duplicate records (1 Viewer)

[loki]

I have a table without a primary key. The fields are id, description and date. There can be more than one record with the same id. I need the code to do a test to the table and if it finds records with the same id, I want it to compare those records date field, and take the record with the oldest date and make the date field value for that record equal to null. Can anyone give me an example of what I might do?

 

[cogent1]

You can use the Find duplicates Query Wizard to set up your duplicates.Have ID and Date Ascending, ID first.

Name your Query "QryDup" then type in the following code in a standard module.

Private Sub NullifyDate

Dim db as database
Dim rs as Recordset
Dim StepUp as Integer
------------------------------------
set db=CurrentDB
set rs =db.OpenRecordset("qryDup")

With rs


If .RecordCount=0 then
MsgBox "No Records"
Exit Sub
End If
.MoveFirst
For StepUp= 1 to .RecordCount/2 'step through half of the records
.Edit
!Date=Null
.Update
.Move 2
Next
End With
End Sub

That should make all the oldest duplicate dates null. This doesn't handle dates that are equal. It makes one of them null. Dates that are already null are not affected. If this is not what you want, get back to me.

HTH