How to show rank order in queries (1 Viewer)

Status
Not open for further replies.

isladogs

MVP / VIP
Local time
Today, 20:16
Joined
Jan 14, 2017
Messages
18,186
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:

Code:
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:

Code:
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

 

Attachments

  • AssessmentRankQueries.zip
    76.9 KB · Views: 586
  • QueryTiedRANK.Example.PNG
    QueryTiedRANK.Example.PNG
    33.9 KB · Views: 1,016
  • QueryRANK.Example2.PNG
    QueryRANK.Example2.PNG
    34 KB · Views: 1,076
  • QueryRANK.Example3.PNG
    QueryRANK.Example3.PNG
    11.8 KB · Views: 784

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 16:16
Joined
Oct 17, 2012
Messages
3,276
Something about the field 'AssRank' stinks to high heaven.
 

isladogs

MVP / VIP
Local time
Today, 20:16
Joined
Jan 14, 2017
Messages
18,186
Frothy
Its all in your mind .....
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom