Results 1 to 3 of 3

Thread: rank duplicate

  1. #1
    Join Date
    Jul 2015
    Posts
    1

    Answered: rank duplicate

    Hi all

    I'm try to rank duplicate records like this. Spend a lot of time to research but couldn't find the answer
    Table just has one field

    AAA
    AAA
    BBB
    BBB
    BBB
    CCC

    I want to rank it like this
    AAA 1
    AAA 2
    BBB 1
    BBB 2
    BBB 3
    CCC 1

    So the duplicate will have its rank among the others; 1st dup, 2nd dup, 3rd dup...

    Is is possible that I can do it in just one query?

    Thanks all

  2. Best Answer
    Posted by ranman256

    "The BEST way is to run a MAKE TABLE query and add that rank field, because doing it via a single query, is unstable.
    Then you can use:

    usage:
    DuplicateCounter [Query], [DuplicateFileName], [RankFieldName]
    ie
    DuplicateCounter "qsDupeRank", "Names", "Rank"

    Code:
    Public Sub DuplicateCounter(ByVal pvQry, ByVal pvDupeFld, ByVal pvRankFld)
       'ASSIGN YOUR FIELD NAMES HERE
    'pvDupeFld = "FIELD w duplicates"
    'pvRankFld = "RANK"
    Dim vMsg
    Dim db As Database
    Dim rst   'As Recordset
    Dim qdf As QueryDef
    Dim vCurrDup, vPrevDup
    Dim iRank As Long
    
    DoCmd.Hourglass True
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs(pvQry)
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    
    With rst
        While Not .EOF
            vCurrDup = .Fields(pvDupeFld) & ""
                    
                    '-----------------------
                    'COUNT THE DUPES...
                    '-----------------------
            If vPrevDup = vCurrDup Then         'mark this
                iRank = iRank + 1
            Else
                iRank = 1
            End If
                    
            .Edit
            .Fields(pvRankFld) = iRank
            .Update
            
            vPrevDup = vCurrDup
            vPrevFld = vCurrFld
            
           .MoveNext
        Wend
    End With
    
    Set qdf = Nothing
    Set rst = Nothing
    Set db = Nothing
    DoCmd.Hourglass False
    
    Exit Sub
    ErrRemove:
    MsgBox Err.Description, , mkCLASSNAME & "::DuplicatesCount():" & Err
    DoCmd.Hourglass False
    End Sub
    "


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    The BEST way is to run a MAKE TABLE query and add that rank field, because doing it via a single query, is unstable.
    Then you can use:

    usage:
    DuplicateCounter [Query], [DuplicateFileName], [RankFieldName]
    ie
    DuplicateCounter "qsDupeRank", "Names", "Rank"

    Code:
    Public Sub DuplicateCounter(ByVal pvQry, ByVal pvDupeFld, ByVal pvRankFld)
       'ASSIGN YOUR FIELD NAMES HERE
    'pvDupeFld = "FIELD w duplicates"
    'pvRankFld = "RANK"
    Dim vMsg
    Dim db As Database
    Dim rst   'As Recordset
    Dim qdf As QueryDef
    Dim vCurrDup, vPrevDup
    Dim iRank As Long
    
    DoCmd.Hourglass True
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs(pvQry)
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    
    With rst
        While Not .EOF
            vCurrDup = .Fields(pvDupeFld) & ""
                    
                    '-----------------------
                    'COUNT THE DUPES...
                    '-----------------------
            If vPrevDup = vCurrDup Then         'mark this
                iRank = iRank + 1
            Else
                iRank = 1
            End If
                    
            .Edit
            .Fields(pvRankFld) = iRank
            .Update
            
            vPrevDup = vCurrDup
            vPrevFld = vCurrFld
            
           .MoveNext
        Wend
    End With
    
    Set qdf = Nothing
    Set rst = Nothing
    Set db = Nothing
    DoCmd.Hourglass False
    
    Exit Sub
    ErrRemove:
    MsgBox Err.Description, , mkCLASSNAME & "::DuplicatesCount():" & Err
    DoCmd.Hourglass False
    End Sub

  4. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    does this 'need' to be done in a query. if 'all' this is for is for say reporting, then I'd consider doing this inside a report group

    ..heck you may even be able to do it without using any code seeing as the number resets on change of item
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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