Preparing Rank List

Introduction

We use Autonumber Field in a Table to create automatic sequence numbers for each record entered into the Table. This can be defined as the Primary Key field. The related table field can be assigned these values as Foreign Key to link with the parent Table, so that combined related information can be obtained for Data Views or Reports.  Even if it is a stand-alone table, not part of any relationship, still it is a good idea to create an autonumber field so that the data can be sorted in keyed-in sequence, especially when the data entry date/time is not recorded in the table.

The Running Sum Property

But, when you pull this field into a Query with any filter criteria, then the auto-number Field contents may not be in consecutive order. If you have planned to use the auto-number field on the report for sequence numbers, then you have to look for alternatives. As far as the Report is concerned, this is not an issue, you can easily create one Textbox in the Detail Section of the Report and type the expression =1 in the Control Source property, and set the Running Sum property value to Yes.

If the Query is used as a Data View file or creates an output table from the filtered data with proper sequence numbers then we have to think of devising some tricks to achieve that. I have already created a Function for generating Sequence Numbers for filtered data in a Query column in an earlier Blog Post and you can have it from the link: auto-numbering in Query Column.

The above was only an introduction to what we are going to do here.  What we have seen in the above Auto-numbering example is assigning sequence numbers for the entire query output records.  But, what if we want separate sequence numbers for each category of records or records belonging to a particular group?

For example: In a School, the Headmaster would like to know who is the highest rank holder in each Subject in a particular Class/School.  Or to find the top 5 State-level student Rank holders of the Schools in each Subject.  Or you have invited quotations for supplying electronic goods and you would like to know item-wise lowest Quotes from among several suppliers.

To do this we can write a VBA Function to work on a report source table with a new data field: Rank, to write out the rank list values.  First, the Data Table must be prepared by combining information from input Tables/Queries, etc.  A sample image of a Students’ Table with several subjects is given below, 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 as 1,2,3 etc., based on the highest values in the Score field (sorted in descending order) for each group of subjects in the Event field (sorted in ascending order).  This rank list is prepared for Class No.2 involving 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 for the presence of an Index 'MyIndex' in the input table. If not found, then creates the Index, for temporary use.  After creating the rank list on the table, we delete the temporary Index “MyIndex” from the table.

The result of the run of RankList("SchoolTable","Events","Score","School") Function is given below.  Check the Rank field values based on the Score field values of the Event field.

The first two ranks are obtained for Accounting from the City View School, 3rd rank by Krum School, 4,5 & 6 awarded to Holiday School.  The seventh rank is shared by City View and Holiday Schools.

Similarly, Current Events and Social Studies are also listed according to their order of rank.

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.