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.

 School Data1

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.

 

 School Data1 Sorted and Ranked

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.


Download Demo Database – Access 2007