TIP How to show rank order in a query (1 Viewer)

isladogs

CID Moderator
Staff member
Local time
Today, 00:30
Joined
Jan 14, 2017
Messages
12,544
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:

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

Attachments

Last edited:

arnelgp

error reading drive A:
Local time
Today, 07:30
Joined
May 7, 2009
Messages
8,727
The problem is that not all recordset support .absoluteposition
 

isladogs

CID Moderator
Staff member
Local time
Today, 00:30
Joined
Jan 14, 2017
Messages
12,544
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

error reading drive A:
Local time
Today, 07:30
Joined
May 7, 2009
Messages
8,727
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:
Code:
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

error reading drive A:
Local time
Today, 07:30
Joined
May 7, 2009
Messages
8,727
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:
Code:
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

CID Moderator
Staff member
Local time
Today, 00:30
Joined
Jan 14, 2017
Messages
12,544
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:
Code:
SELECT serialize("query1","names",[names]) AS Rank, Table1.Names, Table1.Scores FROM query1;
or using table1 direct
Code:
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

Code:
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
Code:
SELECT serialize("table1","scores",[scores]) AS Rank, Table1.Names, Table1.Scores FROM Table1;
This gives the following result:

Code:
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:
Code:
 SELECT table1.names, table1.scores FROM table1 ORDER BY table1.scores DESC;
Query2
Code:
 SELECT serialize("query1","scores",[scores]) AS Rank, Query1.Names, Query1.Scores FROM Query1;
IMHO this gives a more useful result

Code:
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! :D :rolleyes: :)
 

Attachments

Last edited:

arnelgp

error reading drive A:
Local time
Today, 07:30
Joined
May 7, 2009
Messages
8,727
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

CID Moderator
Staff member
Local time
Today, 00:30
Joined
Jan 14, 2017
Messages
12,544
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom