Rank Order Query

[General AI]

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]

Hello and welcome to the forum
I've moved your post to a new thread rather than have it hijack the totally different question asked by another member

As you can tell from the title I've just assigned this, you need a rank order query.
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

I find the 2nd method easier to use

 

[vba_php]

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:

SELECT name, mark FROM table ORDER BY mark DESC

which you DO have on your website, but some of your stuff like this:

SELECT [qryJSONFileTables], etc...

might confused the dude. "JSON"?? 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.

 

[isladogs]

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

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.