Deleting duplicate records based on lowest number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can some one assist with deleting duplicate records based on lowest number.
If field F1 are equal then only keep the record with the highest value in
field F0.

This module works partially. If there are more than 2 duplicates for the
same field F1 I have to run this module again and again until all duplicates
are deleted.

Option Compare Database

Sub LoopTrought()
On Error GoTo LoopTrought_Err:

Dim db As Database
Dim rst As Recordset
Dim sql As String

Dim flgFirst As Boolean

Dim varF0 As Integer
Dim varF1 As String

Dim varF0MAX As Integer
Dim varF1MAX As String

Dim varSelect As String
varSelect = "Select * from tblDUP"

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblDUP", dbOpenDynaset)

flgFirst = True

Do Until rst.EOF

varF0 = rst!F0
varF1 = rst!F1

If flgFirst = True Then
varF0MAX = varF0
varF1MAX = varF1
flgFirst = False
Else

If varF1MAX = varF1 Then
If varF0MAX >= varF0 Then
rst.Edit
rst.Delete
Else
sql = ("Delete * From tblDUP Where tblDUP.F1 = '" _
& varF1MAX & "'" & " and tblDUP.F0 = " & varF0MAX)
db.Execute sql
End If
varF0MAX = varF0
Else
varF0MAX = varF0
varF1MAX = varF1
End If
End If

rst.MoveNext

Loop

LoopTrought_Exit:
Set db = Nothing
Set rst = Nothing
Exit Sub

LoopTrought_Err:
MsgBox "Error # " & Err.Number & ": " & Err.Description
Resume LoopTrought_Exit
End Sub


Is there are way to automatically get a count of the highest number of
duplicate record and than run this modules that number of time.
Or
Is there a better way to do this?
I was thinking if the records are grouped by F1 and then F0. But when I
debug the records don’t go one after another.

I read couple of other posts. But I need to have this automated. Because
the original table will be refreshed each time and could contain duplicate
records.


Thanks in advance,
 
Net said:
Can some one assist with deleting duplicate records based on lowest number.
If field F1 are equal then only keep the record with the highest value in
field F0.

This module works partially. If there are more than 2 duplicates for the
same field F1 I have to run this module again and again until all duplicates
are deleted.
Click to expand...

Thanks in advance,
Click to expand...


Net,

Ok, I'm not 100% clear on what your asking, so I'm interpreting.

It sounds like you want to delete all rows where column F1 has
duplicates, except the row where column F0 has the maximum value for
all occurences of a duplicate F1 value. (I realize that the thread
title says lowest number, but the code is all referring to "max").

Column F0 may not have duplicate values, and its values must be
ascending (for max to work; otherwise, use min for descending values).

CREATE TABLE tblDUP
(F0 AUTOINCREMENT
,F1 DATETIME
,CONSTRAINT pk_tblDUP PRIMARY KEY (F0)
)

Sample Data

F0 F1
1 01/01/2005
2 01/01/2005
3 01/01/2005
4 01/02/2005
5 01/02/2005
6 01/03/2005
7 01/04/2005

This query should get rid of rows 1, 2, and 4.

DELETE *
FROM tblDUP AS D0
WHERE D0.F0 <>
(SELECT MAX(D1.F0)
FROM tblDUP AS D1
WHERE D1.F1 = D0.F1
GROUP BY D1.F1
HAVING COUNT(D1.F1) > 1);

And, when we execute it, the data comes up:

F0 F1
3 01/01/2005
5 01/02/2005
6 01/03/2005
7 01/04/2005


Again, my apologies if I did not correctly understand your questions
or needs.


Sincerely,

Chris O.
 
Back
Top