Rank Order Query (1 Viewer)

General AI

New member
Local time
Today, 14:33
Joined
Dec 14, 2019
Messages
1
i have a record that displays students' marks like

Name Mark

A 40
C 30
F 40
G 25
B 50
S 35

i want a query that can show who is 1st, 2nd etc. like this

Name Mark Position

B 50 1st
A 40 2nd
F 40 2nd
S 35 4th
C 30 5th
G 25 6th
thank you for your kind help.
 

isladogs

MVP / VIP
Local time
Today, 21:33
Joined
Jan 14, 2017
Messages
18,186

vba_php

Forum Troll
Local time
Today, 16:33
Joined
Oct 6, 2019
Messages
2,884
There are two main methods of doing this
a) using a subquery - http://allenbrowne.com/ranking.html
b) using the Serialize function - http://www.mendipdatasystems.co.uk/rank-order-queries/4594424063
Colin,

those might actually a bit more complex than what he understands. for instance, 2 of the colums he wants outputted can be done by simply writing:
Code:
SELECT name, mark FROM table ORDER BY mark DESC
which you DO have on your website, but some of your stuff like this:
Code:
SELECT [qryJSONFileTables], etc...
might confused the dude. "JSON"?? :p I didn't know that was relevant for Access! Do you also have XML markup examples on your site? perhaps having him write a very simple SELECT statement and implement the DCOUNT() function in a temp column like in the attachment would help out?

either way though, he can choose.
 

Attachments

  • Display-Row-Numbers-of-Records.zip
    62.3 KB · Views: 92

isladogs

MVP / VIP
Local time
Today, 21:33
Joined
Jan 14, 2017
Messages
18,186
Adam
Possibly though the OP can speak for himself/herself.

Your code based on DCount is indeed another way of dealing with rank order.
However, as you will be aware, using domain functions in queries is slow, especially if you have large datasets.
Nevertheless, it would be worth me adding that method to my website article.

Anyway, the sample database on that web page gives actual examples which, by chance, are also for student marks.

As for the JSON query code, I could (perhaps should) have written example code like
Code:
SELECT Serialize("QueryName","RankFieldName",[RankFieldName]) AS Rank, Field1, Field2, Field3, RankFieldName
FROM QueryName

However I gave an actual example used in my commercial JSON Analysis app available from http://www.mendipdatasystems.co.uk/json-analyse-transform/4594138310

FYI, just like XML, JSON files can be imported into Access, but there are no native tools to manage the import and transform. My app handles the process in the absence of Access providing the necessary functionality.
 
Last edited:

Users who are viewing this thread

Top Bottom