Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

 

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

 

Code Samples for Businesses, Schools & Personal Use

Updated 09/06/2018              

 

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

 

Code:

Rank Order In Queries

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

 

 

a) qryY7ScAss1MarkRANK

This sorts 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

QueryRANK.Example3 QueryRANK.Example2 QueryTiedRANK.Example

b) qryY7ScAss1PupilRANK

This is the same data but this time ranked in descending mark order by PupilID - so no repeated values for rank

c) qryY7HiAvgAssMarksRANK

This shows Y7 History average marks by assessment in rank order

 

Click to download AssessmentRankQueries.zip    (Approx 0.85MB zipped)

Return to Code Samples Page