Mendip Data Systems
Database applications for businesses and schools
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
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
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)