how to position students rcords from a query

[macitaa]

Good day all,


I have a student database for my school and i have a query with the students name, class, gender, total, average, all calculated.
But i need to derive the position for the students either via total or average and i don't have a clue as to what function to use in getting the 1st, 2nd, 3rd, and so on position for a class of at least 30 students. I am migrating this database from Excel where i usually use the rank and lookup function to get the same, but i am at a dead end with ms access.

Please i need all the help you can render or a referral to where this has been resolved before.
Thanks to you.

[June7]

Ranking in Access is not simple. Review: http://allenbrowne.com/ranking.html

[isladogs]

Have a look at this post of mine on another forum.
https://www.access-programmers.co.uk...23&postcount=1

It uses a function called Serialize to do ranking

Although the methods on Allen browne's website work well, they can be difficult to setup e.g. Using subqueries
I think using Serialize is easier

EDIT I also used the same example in this recent post here
https://www.accessforums.net/showthr...226#post382226

[June7]

Well, Allen does offer a VBA approach but it uses a 'temp' table.

Calling custom function from query has its own drawbacks. Slow performance with large datasets is one possibility but then the same is true with nested subqueries and domain aggregate functions. I have used DCount() to create a row ID.

However, looks like a very nice function.

Just have to try each and see which works best.

[isladogs]

From experience, it's fast even with large datasets
Faster than subqueries certainly.

BTW I didn't write the function and can't remember the author's name