By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,400 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Rankings & missing out numbers

P: n/a
It may be the nasty head cold, but I've got a problem I can't seem to
bend my brain around: We've got a list of students and their average
exam marks (calculated in another part of the database on the fly).
What we need to do is rank them, and that's the bit I'm having trouble
with. Even though we're calculating the average to two decimal places,
sometimes two or more students will have the same average. e.g.

Tom Davis 82.35
Alex Taylor 80.21
Jane Doe 80.21
Sue Brown 80.21
Sally Smith 70.82
What we'd like is for Tom to be '1', Alex, Jane and Sue to be '2' and
Sally to be '5'.

Is this something I can do with some VBA code or can Access maybe do it
on it's own?

Any pointers in the right direction would be most helpful and appreciated!

-Jen (using Access 2003; to reply by email, remove the spork from my
address)
Nov 6 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
See:
Ranking or numbering records
at:
http://allenbrowne.com/ranking.html#query

I think the 'Ranking in a Query' example is the one you are looking for.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jen P." <jp********@cam.ac.ukwrote in message
news:ge**********@gemini.csx.cam.ac.uk...
It may be the nasty head cold, but I've got a problem I can't seem to bend
my brain around: We've got a list of students and their average exam
marks (calculated in another part of the database on the fly). What we
need to do is rank them, and that's the bit I'm having trouble with. Even
though we're calculating the average to two decimal places, sometimes two
or more students will have the same average. e.g.

Tom Davis 82.35
Alex Taylor 80.21
Jane Doe 80.21
Sue Brown 80.21
Sally Smith 70.82
What we'd like is for Tom to be '1', Alex, Jane and Sue to be '2' and
Sally to be '5'.

Is this something I can do with some VBA code or can Access maybe do it on
it's own?

Any pointers in the right direction would be most helpful and appreciated!

-Jen (using Access 2003; to reply by email, remove the spork from my
address)
Nov 6 '08 #2

P: n/a
On Thu, 06 Nov 2008 11:22:14 +0000, "Jen P." <jp********@cam.ac.uk>
wrote:

Easiest way is to use a recordset and loop over the records. Off the
top of my head:
dim rs as dao.recordset
dim intRank as integer
dim intNextRank as integer
dim sngScore as single
set rs=currentdb.openrecordset("MyQuery", dbOpenDynaset)
intRank=1
intNextRank=1
sngScore=rs!Score
while not rs.eof
if rs!Score<>sngScore then
intRank=intNextRank
sngScore=rs!Score
end if
rs.Edit
rs!Rank = intRank
rs.Update
rs.movenext
intNextRank = intNextRank + 1
wend
rs.close
set rs=nothing

>It may be the nasty head cold, but I've got a problem I can't seem to
bend my brain around: We've got a list of students and their average
exam marks (calculated in another part of the database on the fly).
What we need to do is rank them, and that's the bit I'm having trouble
with. Even though we're calculating the average to two decimal places,
sometimes two or more students will have the same average. e.g.

Tom Davis 82.35
Alex Taylor 80.21
Jane Doe 80.21
Sue Brown 80.21
Sally Smith 70.82
What we'd like is for Tom to be '1', Alex, Jane and Sue to be '2' and
Sally to be '5'.

Is this something I can do with some VBA code or can Access maybe do it
on it's own?

Any pointers in the right direction would be most helpful and appreciated!

-Jen (using Access 2003; to reply by email, remove the spork from my
address)
Nov 6 '08 #3

P: n/a
Cheers, Allen and Tom! My cold-virus-addled brain appreciates your
help. ;) I'll have a look at both options and see what I can do with
them. :)

-Jen
Nov 7 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.