Results 1 to 3 of 3
  1. #1
    Join Date
    May 2016
    Posts
    1

    Unanswered: Help With Access Query - Remove duplicates but leave the unique

    Hi,

    I have a following table in MS Access 2007 where I want to remove the duplicates (from a column - "Merge") from it but leave all the unique values in it. Thanks heaps for looking into it.

    Table Name=Disc_Analysis
    ID | Name | Date | Amount | Merge
    1 | Adam | 05/05/2015 | $55 | 1Adam5555
    2 | Ram | 07/04/2014 | $400 | 2Ram4525
    3 | Roy |05/05/2015 | $55 | 1Adam5555
    4 | Mat | 07/11/2014 | $499 | 4MatCBOM

    I want to see the following as my result table.
    ID | Name | Date | Amount | Merge
    1 | Adam | 05/05/2015 | $55 | 1Adam5555
    2 | Ram | 07/04/2014 | $400 | 2Ram4525
    4 | Mat | 07/11/2014 | $499 | 4MatCBOM

    Thank you so much in advance.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    472
    Provided Answers: 22
    paste this code into a module
    build a query that sorts the data with the dupes,...sort by [merge],[id] fields
    this code will scan the list and put an X in the MARK field if its a dupe.

    create a new field in your table called MARK, str(1)
    then delete all records with this mark.
    usage:

    MarkDuplicates "qsSortedDupes","merge","Mark"


    Code:
    Public Sub MarkDuplicates(ByVal pvQry, ByVal pvDupeFld, ByVal pvChgFld)
    'pvQry = query name
    'pvDupeFld   = field with duplicate values
    'pvChgFld    = field to change when duplicate is found
    
    Dim db As Database
    Dim rst   'As Recordset
    Dim qdf As QueryDef
    Dim vCurrDup, vPrevDup, vKey, vCurrFld, vAddr
    
    DoCmd.Hourglass True
    Set db = CurrentDb
    Set qdf = db.QueryDefs(pvQry)
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    vPrevDup = "*&%"
    
    With rst
        While Not .EOF
            vCurrDup = .Fields(pvDupeFld) & ""
            vKey = UCase(.Fields(pvFld2Check)) & ""
    
            If vCurrDup <> "" Then
    
     '-----------------------
     'DONT MARK THE DUPES...ONLY MARK THE 1ST RECORD
     '-----------------------
               If vPrevDup <> vCurrDup Then
                        .Edit
                        .Fields(pvChgFld) = "X"
                        .Update
               End If
            End If
            vPrevDup = vCurrDup
    
           .MoveNext
        Wend
    End With
    DoCmd.Hourglass False
    
    Set qdf = Nothing
    Set rst = Nothing
    Set db = Nothing
    End Sub

  3. #3
    Join Date
    May 2016
    Posts
    81
    Provided Answers: 3
    Hi parthiv

    I propose you this solution

    With
    1. Two queries
      1. qryFirst = SELECT First(doublons.ID) AS ID FROM doublons GROUP BY doublons.Merge ORDER BY First(doublons.ID) ASC
      2. qryDelDoublons = DELETE FROM doublons WHERE ID NOT IN (SELECT ID FROM qryFirst)


      You can use also use MIN instead of First in qryFirst. Perhaps better ???

    2. One query
      1. Or qryDelDoublons = DELETE FROM doublons WHERE ID NOT IN (SELECT First(doublons.ID) AS ID FROM doublons GROUP BY doublons.Merge ORDER BY First(doublons.ID))

    Last edited by informer; 05-26-16 at 08:53.

Posting Permissions

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