Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    3

    Post Unanswered: Removing duplicates while retaining one with the latest date

    I am having a headache working on a huge database which contains 120000 records. the big problem is that the database contains duplicates of the "serial no" of products. Another important field is the "date_purchased". How do I remove the duplicates and retain the serial no with the latest date purchased? I have managed to remove duplicates but the program does not remove on the basis of the date_purchased.

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i can't find a SQL method for doing it in access

    but this should work (if i can figure out why "rs1.FindNext "serno = " & rs2!serno & " and date <> " & rs2!testdate" reports error '3251')

    Sub Delete()
    Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
    Set rs1 = CurrentDb.OpenRecordset("table1", dbOpenTable)
    Set rs2 = CurrentDb.OpenRecordset("SELECT serno, Max(date) AS testdate FROM Table1 GROUP BY Table1.serno HAVING Count(Table1.serno)>1;")
    rs1.MoveFirst
    rs2.MoveFirst
    While Not rs2.EOF
    Do
    rs1.FindNext "serno = " & rs2!serno & " and date <> " & rs2!testdate
    If rs1.NoMatch Then
    Exit Do
    Else
    rs1.Delete
    End If
    Loop
    rs2.MoveNext
    Wend
    End Sub
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try brute force! something like:

    rex is a recordset based on the table sorted by serial_number (either way) then date_purchased (descending)

    dim currentSerial as string ' or long or... whatever the serial_number is
    with rex
    .movefirst
    do while not .EOF
    if !serial_number = currentSerial then
    .delete
    else
    currentSerial = !serial_number 'note a new serial_number
    .movenext 'don't delete the new serial
    if .EOF then exit do
    endif
    .movenext
    loop 'until EOF
    end with

    i didn't test it!

    izy

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe i should have tested it!!
    this looks smarter:

    with rex
    .movefirst
    do while not .EOF
    if !serial_number = currentSerial then
    .delete
    .movenext 'is added
    else
    currentSerial = !serial_number 'note a new serial_number
    .movenext 'don't delete the new serial
    ' "if .EOF then exit do" is gone
    endif
    ' .movenext is gone
    loop 'until EOF
    end with


    izy

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    although the logic escapes me the code now works,

    Sub Delete()
    Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
    Set rs1 = CurrentDb.OpenRecordset("table1", 2)
    Set rs2 = CurrentDb.OpenRecordset("SELECT serno, Max(perdate) AS testdate FROM Table1 GROUP BY Table1.serno HAVING Count(Table1.serno)>1;")
    If rs2.RecordCount > 0 Then
    rs1.Move -1
    rs2.MoveFirst
    While Not rs2.EOF
    Do
    If rs1.BOF Then
    rs1.FindFirst "serno = " & rs2!serno
    Else
    rs1.FindNext "serno = " & rs2!serno
    End If
    If rs1.NoMatch Then
    Exit Do
    Else
    If rs1!perdate < rs2!testdate Then
    rs1.Delete
    End If
    End If
    Loop
    rs2.MoveNext
    Wend
    End If
    End Sub

    EDIT: Added some error handling to code

    anyone know why you can't do a find on a table type recordset?
    Last edited by m.timoney; 06-16-03 at 11:54.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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