Rankings & missing out numbers

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." wrote in message
news:geuk16$7j6$1@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)

On Thu, 06 Nov 2008 11:22:14 +0000, "Jen P."
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)

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