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:
Accept a source table or query.
Accept grouping and sorting fields.
Accept ranking style (Dense / Competition / Sequential).
Execute ranking.
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.
Clean Object-Oriented Design
Encapsulates ranking behavior into a single reusable object.
Compatible with Linked Tables
Uses dbOpenDynaset, so it works with ODBC sources.
Extensible
Future enhancements may include:
Multiple group levels
Dynamic ORDER BY clauses
Returning ranked recordsets instead of updating tables
Error handling classes
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.
Clean architecture
Reusability
Maintainability
Professional-grade design
For developers who build modular Microsoft Access applications, this approach promotes:
