Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    13

    Question Unanswered: How to eliminate partly duplicate records in order to leave any 3 of them?

    Hi,

    I am trying to eliminate partly duplicate records in order to leave ANY THREE of them (if any). If there are less than 3 of them ...then whatever exist. I do not care other criteria...

    Memebers have duplicates under [MEMBER ID ]field

    For example I would like to delete those ones marked by *

    Member ID DATE

    *11 12/10/2004
    *11
    11 12/10/2004
    11 11/1/2003
    11 11/1/2003
    22
    22
    *33 5/1/2001
    *33 12/10/2003
    *33 12/9/2004
    33 11/1/2004
    33 5/1/2001
    33 8/1/2004

    Thank you in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ok Katrin how about this

    Code:
    Sub deletebarTop3()
        Dim cmd As ADODB.Command
        Dim rst As Recordset
        Dim strSQL As String
        
        Set cmd = New ADODB.Command
        Set rst = New ADODB.Recordset
        
        'fill a recordset with distinct member ID's
        cmd.ActiveConnection = CurrentProject.Connection
        strSQL = "SELECT DISTINCT [MEMBER ID] FROM tbltest3"
        cmd.CommandText = strSQL
        Set rst = cmd.Execute
        
        'create a SQL Statement to delete all bar top 4
        With rst
                .MoveFirst
                Do While Not .EOF
                strSQL = "DELETE * FROM tbltest3 " & _
                            "WHERE [MEMBER ID] = " & .Fields(0) & " AND " & _
                            "primKey NOT IN (SELECT TOP 3 primKey FROM tblTest3 " & _
                                "WHERE [MEMBER ID] = " & .Fields(0) & " " & _
                                "ORDER BY primKey DESC)"
                cmd.CommandText = strSQL
                cmd.Execute
                .MoveNext
            Loop
            .Close
        End With
        
        Set rst = Nothing
        Set cmd = Nothing
    End Sub
    you must have a primary key on your table for this code to work
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •