Preparing a Rank List.
We often use an AutoNumber field in a table to automatically generate a unique sequence number for each record entered. This field can serve as the Primary Key. In related tables, a corresponding field can be designated as a Foreign Key to establish a relationship with the parent table, enabling combined views of related data forms, queries, or reports.
Even in a stand-alone table that is not part of any relationship, using an AutoNumber field is beneficial. It allows records to be sorted in the order they were entered, which is especially useful when the table does not include a date/time field for data entry.
The Running Sum Property.
However, when you pull an AutoNumber field into a query with any filter criteria, its values may no longer appear in consecutive order. If you intend to use the AutoNumber field for sequence numbers on a report, this isn’t a problem. You can simply add a textbox in the Detail Section of the report, set its Control Source to =1 and set the Running Sum property to Yes—Access will automatically number the report rows sequentially.
If the query is being used as a data source for a Data View or to create an output table with properly sequenced numbers, we need additional techniques to achieve correct numbering. In an earlier blog post, I shared a Function to generate sequence numbers for filtered query results. You can refer to it here: [Auto-numbering in Query Column].
The discussion introduces the concept of assigning sequence numbers across all records in a query. But what if we want separate sequence numbers for each category or group of records?
For example:
-
In a school, the headmaster may want to identify the highest-ranked holder in each subject for a particular class or school.
-
Or, find the top 5 state-level rank holders in each subject across schools.
-
In a procurement scenario, you want to identify the lowest quotes for each item from multiple suppliers.
To achieve this, we can write a VBA function that works on a report source table, adding a new field (e.g., Rank) to store the ranking. First, the data table must be prepared by consolidating information from input tables or queries.
Below is a sample image of a student’s table with several subjects, ready for running the Rank-List program.
The Rank List.
Our task is to organize the above data in a specified order and assign Rank numbers (1, 2, 3, …) based on the highest values in the Score field, sorted in descending order. This ranking should be done separately for each group of subjects in the Event field, which is sorted in ascending order.
The rank list is being prepared for Class No. 2, covering students from several schools in the area.
Table Name: SchoolTable
Sorting Order: The Event (Ascending), Score (Descending), School (Ascending) – School field sorting optional
Function Call Syntax: RankList(TableName, Primary Sorting Field, Value Field, Optional Third Sorting Field)
Sample Function Call: RankList(“SchoolTable”, ”Events”, ”Score”, ”School”)
The RankList() Function
The RankList() Function Code is given below:
Public Function RankList(ByVal TableName As String, _
ByVal Grp1Field As String, _
ByVal ValueField As String, _
Optional ByVal Grp2Field As String)
'-----------------------------------------------------------------
'Preparing Rank List
'Author : a.p.r.pillai
'Date : August 2011
'Rights : All Rights Reserved by www.msaccesstips.com
'Remarks: Free to use in your Projects
'-----------------------------------------------------------------
'Parameter List:
'TableName : Source Data Table
'Grp1Field : Category Group to Sort on
'ValueField : On which to determine the Rank Order
'Grp2Field : Sorted on for values with the same rank number
'-----------------------------------------------------------------
Dim db As Database, rst As Recordset, curntValue, prevValue
Dim srlRank As Byte, curntGrp1, prevGrp1
Dim prevGrp2, curntGrp2
Dim fld As Field, tbldef As TableDef, idx As Index
Dim FieldType As Integer
On Error Resume Next
Set db = CurrentDb
Set rst = db.OpenRecordset(TableName, dbOpenTable)
'Check for presence of Table Index "MyIndex"
'if not found then create
rst.Index = "MyIndex"
If Err > 0 Then
Err.Clear
On Error GoTo RankList_Err
Set tbldef = db.TableDefs(TableName)
Set idx = tbldef.CreateIndex("MyIndex")
FieldType = rst.Fields(Grp1Field).Type
Set fld = tbldef.CreateField(Grp1Field, FieldType)
idx.Fields.Append fld
FieldType = rst.Fields(ValueField).Type
Set fld = tbldef.CreateField(ValueField, FieldType)
fld.Attributes = dbDescending ' Line not required for sorting in Ascending
idx.Fields.Append fld
FieldType = rst.Fields(Grp2Field).Type
Set fld = tbldef.CreateField(Grp2Field, FieldType)
idx.Fields.Append fld
rst.Close
tbldef.Indexes.Append idx
tbldef.Indexes.Refresh
Set rst = db.OpenRecordset(TableName, dbOpenTable)
rst.Index = "MyIndex"
End If
curntGrp1 = rst.Fields(Grp1Field)
prevGrp1 = curntGrp1
curntValue = rst.Fields(ValueField).Value
prevValue = curntValue
Do While Not rst.EOF
srlRank = 1
Do While (curntGrp1 = prevGrp1) And Not rst.EOF
If curntValue < prevValue Then
srlRank = srlRank + 1
End If
rst.Edit
rst![Rank] = srlRank
rst.Update
rst.MoveNext
If Not rst.EOF Then
curntGrp1 = rst.Fields(Grp1Field)
prevValue = curntValue
curntValue = rst.Fields(ValueField).Value
End If
Loop
prevGrp1 = curntGrp1
prevValue = curntValue
Loop
rst.Close
'Delete the Temporary Index
tbldef.Indexes.Delete "MyIndex"
tbldef.Indexes.Refresh
Set rst = Nothing
Set db = Nothing
RankList_Exit:
Exit Function
RankList_Err:
MsgBox Err & " : " & Err.Description, , "RankList()"
Resume RankList_Exit
End FunctionThe Code Creates a Temporary Index
In the first part of the program, we check whether an index named MyIndex exists in the input table. If the index is not found, the program creates it temporarily for use during the ranking process. Once the rank list has been generated in the table, the temporary index MyIndex is deleted.
The result of running the function:
is shown below. Observe the Rank field values, which are assigned based on the Score values within each Event group.
In the Accounting event, the first two ranks are awarded to City View School, and the third rank goes to Krum School. The fourth, fifth, and sixth ranks are assigned to Holiday School. The seventh rank is shared by City View and Holiday Schools.
Similarly, Current Events and Social Studies are ranked in the same order, with each school receiving its rank based on its scores in its respective event.
Download.
Download Demo RankList.zip
