Preparing Rank List

Introduction

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 above 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.

Right-click to open Large Image in New Tab/Window.

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 Function

The 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:

RankList("SchoolTable", "Events", "Score", "School")

is shown below. Observe the Rank field values, which are assigned based on the Score values within each Event group.

Right-click to open Large Image in New Tab/Window.

In the Accounting event, the first two ranks are awarded to City View School, the third rank goes to Krum School, and the fourth, fifth, and sixth ranks are assigned to Holiday School. The seventh rank is shared by City View and Holiday Schools.

Similarly, the events Current Events and Social Studies are also ranked in order, with each school receiving ranks based on its scores within the respective event.

Download.

Download Demo RankList.zip

1. Auto-Numbering in Query Column
2. Product Group Sequence with Auto-Numbers.
3. Preparing Rank List.
4. Auto-Number with Date and Sequence Number.
5. Auto-Number with Date and Sequence Number-2.