Group-Wise Ranking Using Class Module

Group-Wise Ranking in Microsoft Access Using a Class Module

A Reusable Object-Oriented Approach.

In earlier articles, we explored how to prepare a Rank List in Microsoft Access using procedural VBA. While that method works reliably, modern Access application design benefits greatly from encapsulating business logic inside Class Modules.

Unlike database engines such as Microsoft SQL Server, which provide built-in ranking functions like RANK() and ROW_NUMBER(), Microsoft Access does not natively support group-wise ranking in SQL.

This article demonstrates how to implement ranking logic using a dedicated class module, making the solution:

• Reusable

• Maintainable

• Extensible

• Compatible with local and linked tables

Why Use a Class Module?

Moving ranking logic into a class provides:

✔ Encapsulation of ranking behavior.

✔ Reusability across forms, reports, and automation tasks.

✔ Cleaner separation of concerns.

✔ Easier debugging and future enhancement.

This design is especially useful in structured Access applications that follow modular architecture principles.

Design Overview

The class will:

1. Accept a source table or query.

2. Accept grouping and sorting fields.

3. Accept ranking style (Dense / Competition / Sequential).

4. Execute ranking.

5. Update a designated Rank field.

Step 1: Create the Class Module.

Create a new Class Module and name it:

clsRankList

Step 2: Insert the Following Code.

Option Compare Database

Option Explicit


Private mSource As String

Private mGroupField As String

Private mValueField As String

Private mRankField As String

Private mRankingType As String


Public Property Let SourceTable(ByVal vValue As String)

    mSource = vValue

End Property


Public Property Let GroupField(ByVal vValue As String)

    mGroupField = vValue

End Property


Public Property Let ValueField(ByVal vValue As String)

    mValueField = vValue

End Property


Public Property Let RankField(ByVal vValue As String)

    mRankField = vValue

End Property


Public Property Let RankingType(ByVal vValue As String)

    mRankingType = UCase(vValue)

End Property


Public Sub ExecuteRanking()

    Dim db As DAO.Database

    Dim rs As DAO.Recordset

    Dim strSQL As String


    Dim CurrentGroup As Variant

    Dim PreviousValue As Variant

    Dim RankNo As Long

    Dim SkipCount As Long


    If mRankingType = "" Then mRankingType = "DENSE"

    Set db = CurrentDb


    strSQL = "SELECT * FROM [" & mSource & "] " & _

             "ORDER BY [" & mGroupField & "], [" & mValueField & "] DESC;"


    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    If rs.EOF Then Exit Sub

    rs.MoveFirst

    CurrentGroup = Null

    PreviousValue = Null

    RankNo = 0

    SkipCount = 0

    Do While Not rs.EOF        

        If Nz(rs(mGroupField)) <> Nz(CurrentGroup) Then        

            CurrentGroup = rs(mGroupField)

            RankNo = 1

            SkipCount = 1

            PreviousValue = rs(mValueField)           

        Else            

            Select Case mRankingType

                Case "DENSE"

                    If rs(mValueField) <> PreviousValue Then

                        RankNo = RankNo + 1

                    End If            

                Case "COMPETITION"

                    If rs(mValueField) <> PreviousValue Then

                        RankNo = SkipCount

                    End If               

                Case "SEQUENTIAL"

                    RankNo = RankNo + 1                 

            End Select            

            SkipCount = SkipCount + 1

            PreviousValue = rs(mValueField)

        End If        

        rs.Edit

        rs(mRankField) = RankNo

        rs.Update       

        rs.MoveNext        

    Loop

    rs.Close

    Set rs = Nothing
    Set db = Nothing   

End Sub

Step 3: Using the Class

From a standard module, form, or automation routine:

Dim objRank As New clsRankList

With objRank

    .SourceTable = "tblStudentMarks"

    .GroupField = "Class"

    .ValueField = "TotalMarks"

    .RankField = "Rank"

    .RankingType = "COMPETITION"

    .ExecuteRanking

End With

Set objRank = Nothing

Supported Ranking Types

RankingType Behavior

DENSE 1, 2, 2, 3

COMPETITION 1, 2, 2, 4

SEQUENTIAL 1, 2, 3, 4

If no ranking type is specified, DENSE is used by default.

Advantages of This Approach.

1. Clean Object-Oriented Design

   Encapsulates ranking behavior into a single reusable object.

2. Compatible with Linked Tables

   Uses dbOpenDynaset, so it works with ODBC sources.

3. Extensible

   Future enhancements may include:

   • Multiple group levels

   • Dynamic ORDER BY clauses

     Returning ranked recordsets instead of updating tables

     Error handling classes

4. Integration-Friendly

   Works well with form wrappers and structured automation routines.

Performance Notes

• Efficient for moderate datasets.

• Ensure indexes exist on GroupField and ValueField.

• For very large datasets, consider server-side ranking.

Final Thoughts

Encapsulating ranking logic inside a class module elevates the solution from a procedural workaround to a structured, reusable component.

For developers who build modular Microsoft Access applications, this approach promotes:

• Clean architecture

• Reusability

• Maintainability

• Professional-grade design

--oOo--

Read More