TIP How to show rank order in a query

[isladogs]

Its easy to create a rank order in Access reports but not so easy in queries
If you Google you will find several methods of doing so, but this is the easiest method I'm aware of

Attached is a simple example database I created in answer to a question on another forum.
It shows how to set a rank order in Access queries using the Serialize function below:

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 Serialize 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;

The example db uses student assessment marks for a fictitious school

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 can be repeated
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

Hope this is enough to show how to use the function

 

[arnelgp]

The problem is that not all recordset support .absoluteposition

 

[isladogs]

AFAIK it will work for all standard select queries.
I've also used it successfully on union queries.

However, I'm aware this won't work on pass-through queries.

 

[arnelgp]

it only account to the position of
record in the recordset and not the actual
ranking.

it fails when there are multiple ties
on the field you are trying to serialize:

table1:
names		scores
-------------------------
a		11
b		11
c		11
d		14
e		15
f		15
g		16
h		16

query1:
select names, scores from table1 order by names, scores;

query2:
select names, scores, serialize("query1","names",[names]) as rank order by names, serialize("query1","names",[names]);

result:
names		scores		Rank
---------------------------------------------
a		11		1
b		11		2
c		11		3
d		14		4
e		15		5
f		15		6
g		16		7
h		16		8

my code:

Public Function myRank(QueryName As String, _
                       KeyName As String, _
                       KeyValue As Variant, FieldToRank As String) As Long
    Dim rs As DAO.Recordset
    Dim lngRank As Long
    Dim FieldValue As Variant
    Dim PreviousValue As Variant
    Set rs = CurrentDb.OpenRecordset(QueryName, dbOpenSnapshot)
    With rs
        .FindFirst BuildCriteria(KeyName, rs.Fields(KeyName).Type, KeyValue)
        While Not .BOF
            FieldValue = .Fields(FieldToRank).Value
            If PreviousValue <> FieldValue Then _
                lngRank = lngRank + 1
            
            PreviousValue = FieldValue
            .MovePrevious
        Wend
        .Close
    End With
    Set rs = Nothing
    myRank = lngRank
End Function

query:
select names, scores, serialize("query1","names",[names],"scores") as rank order by names, serialize("query1","names",[names],"scores");

result:
names		scores		Rank
---------------------------------------------
a		11		1
b		11		1
c		11		1
d		14		2
e		15		3
f		15		3
g		16		4
h		16		4

 

[arnelgp]

it only account to the position of
record in the recordset and not the actual
ranking.

it fails when there are multiple ties
on the field you are trying to serialize:

table1:
names		scores
-------------------------
a		11
b		11
c		11
d		14
e		15
f		15
g		16
h		16

query1:
select names, scores from table1 order by names, scores;

query2:
select names, scores, serialize("query1","names",[names]) as rank order by names, serialize("query1","names",[names]);

result:
names		scores		Rank
---------------------------------------------
a		11		1
b		11		2
c		11		3
d		14		4
e		15		5
f		15		6
g		16		7
h		16		8

my code:

Public Function myRank(QueryName As String, _
                       KeyName As String, _
                       KeyValue As Variant, FieldToRank As String) As Long
    Dim rs As DAO.Recordset
    Dim lngRank As Long
    Dim FieldValue As Variant
    Dim PreviousValue As Variant
    Set rs = CurrentDb.OpenRecordset(QueryName, dbOpenSnapshot)
    With rs
        .FindFirst BuildCriteria(KeyName, rs.Fields(KeyName).Type, KeyValue)
        While Not .BOF
            FieldValue = .Fields(FieldToRank).Value
            If PreviousValue <> FieldValue Then _
                lngRank = lngRank + 1
            
            PreviousValue = FieldValue
            .MovePrevious
        Wend
        .Close
    End With
    Set rs = Nothing
    myRank = lngRank
End Function

query:
select names, scores, serialize("query1","names",[names],"scores") as rank order by names, serialize("query1","names",[names],"scores");

result:
names		scores		Rank
---------------------------------------------
a		11		1
b		11		1
c		11		1
d		14		2
e		15		3
f		15		3
g		16		4
h		16		4

 

[isladogs]

Oh dear arnel

I know your time zone is ahead of that in the UK but I can only assume you've been celebrating New Year a bit early

1. I'm going to rank both of your replies equal first as they are identical
On second thoughts I'm going to rank them equal last as the answers contain errors

2. Did you actually look at my examples?
I specifically mentioned 'tied' values and gave an example to illustrate that

3. Using your example data, your query1 is pointless as its just the same as table1
Also, your query2 contains serialize twice and the syntax is incorrect

So you won't get any results

Correcting your query 2 (2A) and using your query1:

SELECT serialize("query1","names",[names]) AS Rank, Table1.Names, Table1.Scores FROM query1;

or using table1 direct

SELECT serialize("table1","names",[names]) AS Rank, Table1.Names, Table1.Scores FROM table1;

Either of these will give the following pointless though correct values as you are effectively ranking by name

Rank	Names	Scores
1	a	11
2	b	11
3	c	11
4	d	14
5	e	15
6	f	15
7	g	16
8	h	16

4. Instead, rank by scores - you will of course get tied values in this case and that is CORRECT

SELECT serialize("table1","scores",[scores]) AS Rank, Table1.Names, Table1.Scores FROM Table1;

This gives the following result:

Rank	Names	Scores
1	a	11
1	b	11
1	c	11
4	d	14
5	e	15
5	f	15
7	g	16
7	h	16

5. Personally, I'd sort in descending order by scores - do that in query1
Query1:

 SELECT table1.names, table1.scores FROM table1 ORDER BY table1.scores DESC;

Query2

 SELECT serialize("query1","scores",[scores]) AS Rank, Query1.Names, Query1.Scores FROM Query1;

IMHO this gives a more useful result

Rank	Names	Scores
1	h	16
1	g	16
3	f	15
3	e	15
5	d	14
6	c	11
6	b	11
6	a	11

6. Finally, there are many examples of ranking functions out there.
Your own function myRank may or may not be better than Serialize
But you didn't actually use it in your reply ....

BTW - as my reply is full of irony, I won't be in the least surprised if there are errors in my response as well

Happy New Year - hope you stop seeing double soon!

 

[arnelgp]

your code will speak for itself.
look at the results, either their
in sequence (with no tie) or
they have sequence with tie but
broken sequence.

another year ender! BOOM! BANG!

 

[isladogs]

Did you actually read my reply?

As for the supposed 'broken sequence', it's perfectly normal for the next ranked item to be ranked as 3 if two records are ranked equal first.

 

[Ann Simmons]

I like this. I have learned alot. You people are very experienced when it comes to databases.
Question:- what if you want to rank Students according to:-
1. Marks
2. StudentID
3. Years
4. Term
5. Grade
6. Stream
7. Examinationtype.

I get data type mismatch in after creating a function and applying this to a field:-


=Rank([StudentID], [Marks], [Years], [Term], [Grade], [Stream], [Examinationtype]]

 

[isladogs]

You say you have created a function but just gave an expression with 7 arguments.

Ranking can't easily, if at all, be done on multiple fields.
The problem is that each field might well have a different rank order so trying to combine them would be meaningless.

Ranking certainly can't be done on 7 fields using the Serialize function which is designed to rank by one field only.
Suggest you read my Web article which gives more details as well as an example app.

Rank Order In Queries

This article describes a method of creating a rank order in Access queries using a Serialize function

www.isladogs.co.uk

In particular, look carefully at how to handle tied results if that matters to you