Enumerate rows based on field contents

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, this is a re-post except I don't think I described accurately what I'm
trying to achieve in the first post.

I have designed a race results program for a charity and the one thing I
haven't achieved is how to list team results. I need a line number to show
the first, second and third instances for teams as the results appear in time
ascending order. thus:

name finishtime team instance
john 44:50 surrey AC 1
mark 45.55 woking AC 1
Mary 47:05 surrey AC 2
Ben 48:01 surrey AC 3
Bill 48:10 woking AC 2 and so on

I've tried the "=1" but, of course, that doesn't work, merely counts all
records.
Is there a variant on this which would make it simple to achieve what I
want, please? I've searched high and low for a solution but to no avail so
far.

Thanking you for any assistance
 
You may need to add a calculated subquery column in your query:

Instance: (SELECT Count(*) FROM tblResults r WHERE r.Team = tblResults.Team
AND tblResults.FinishTime <=r.FinishTime)
 
Thank you Duane. Just one minor change I had to make ..
=r.FinishTime (rather than <=r.FinishTime)
Click to expand...
to make the quickest time count as "1" and the next quickest "2" etc
(a reverse count)

I'm sorry for the delay in responding but I thought the SQL bit was beyond
me until I found I could use it in query design view grid.

Many thanks. It works!
 
Back
Top