How to show rank order in queries

[isladogs]

NOTE:
This is based on a thread I posted a few weeks ago at
https://www.access-programmers.co.uk/forums/showpost.php?p=1560423&postcount=1
I've copied it to the code repository so its easier to find for future reference

Its easy to create a rank order in Access reports but not so easy in queries.
If you Google the topic you will find several methods of doing so
The standard solution involves the uses of subqueries.
For example, see http://allenbrowne.com/ranking.html

However, the following method using the Serialize function below is in my opinion far easier:

Public Function Serialize(qryname As String, KeyName As String, keyValue) As Long

On Error GoTo Err_Handler

    'used to create rank order for records in a query
    'add as query field
    'Example Serialize("qry1","field1",[field1])
    
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)
    
    rst.FindFirst Application.BuildCriteria(KeyName, rst.Fields(KeyName).type, keyValue)
    
    Serialize = Nz(rst.AbsolutePosition, -1) + 1

    rst.Close
    Set rst = Nothing

Exit_Handler:
    Exit Function
    
Err_Handler:
    MsgBox "Error " & Err.Number & " in Serialize procedure: " & Err.Description
    GoTo Exit_Handler
    
End Function

For example in this query, the function is used to create a TableID field which serves as the row number:

SELECT Serialize("qryJSONFileTables","TableName",[TableName]) AS [TableID], MSysObjects.Name AS TableName, qryJSONFileTableNames.FileID, qryJSONFileTableNames.FileNameFROM qryJSONFileTableNames INNER JOIN MSysObjects ON qryJSONFileTableNames.TableName = MSysObjects.Name
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;

Attached is a simple example database using student assessment marks for a fictitious school to illustrate its use

It has 2 tables - tblAssessmentMarks/tblAssessmentTypes

Various queries including three with rank order fields
a) qryY7ScAss1MarkRANK - puts Y7 Science students in rank order by mark (descending order) - as several students have the same mark, the rank values are repeated e.g. two are ranked 2 so the next is ranked 4

b) qryY7ScAss1PupilRANK - same data but this time ranked in descending mark order by PupilID - so no repeated values for rank

c) qryY7HiAvgAssMarksRANK - Y7 History average marks by assessment in rank order

 

[Frothingslosh]

Something about the field 'AssRank' stinks to high heaven.

 

[isladogs]

Frothy
Its all in your mind .....