Updated 9 June 2018
This article was originally published back in Jan 2018 as 'How to Show Rank Order in Queries' at Access World Forums.
Its easy to create a rank order in Access reports but not so easy in queries.
If you Bing/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. In addition, it is usually faster than using subqueries
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
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
Set rst = Nothing
MsgBox "Error " & Err.Number & " in Serialize procedure: " & Err.Description
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 further illustrate its use
It has 2 tables: tblAssessmentMarks / tblAssessmentTypes and various queries including three with rank order fields.
This sorts Year 7 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
This is the same data but this time ranked in descending mark order by PupilID - so no repeated values for rank
This shows Y7 History average marks by assessment in rank order
Rank order queries can also be used as record sources for forms (or reports).
Conditional formatting can also be used to assist understanding.
In the example form shown below, rank values ending in 5 are coloured blue and those ending in 0 are coloured green.
Click to download: AssessmentRankQueries.zip (Approx 0.85MB zipped)
Colin Riddington Mendip Data Systems Last Updated 9 June 2018