Preparing Rank List
We use Autonumber Field in a Table to create automatic sequence numbers for each record entered into the Table. This can be defined as Primary Key field. 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 relationships, 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 with the table.
But, when you pull this field into a Query with some filter criteria then the autonumber Field contents may not be in consecutive order. If you have planned to use the autonumber 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 a 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 the Query is used as a Data View file or create 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 belongs 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 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 our RankList() Program.
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: 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 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
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.
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 Event field.
The first two ranks for Accounting are obtained by City View School, 3rd rank by Krum School, 4,5 & 6 awarded to Holiday School. Seventh rank is shared by City View and Holiday Schools.
Similarly, Current Events and Social Studies are also listed according to their order of ranks.