Ranking of Finishers with equal time (1 Viewer)

[Lightwave]

Hello everyone

Another questions about a race timing system

4 finishers

In a report or a form what do you believe is the optimal way to rank finishers such that people with the same time are marked equally

eg
1 - John
2 - Bill
2 - Colin
4 - Thomas

With the ranking running automatically off a completion time field/calculation with the competitors in 2nd equal position having equal time. To be constructed in such a way that there could be infinite no of competitors with the same time such that a in a race of 101 competitors with 100 finishers crossing at the front at the same time they would all be ranked 1st and the next competitor would be ranked 101st.

I was thinking rank by time. Calculate time between ascending list count from 1 if zero between ascending list maintain count else use count...

Just mulling over ideas.

 

[Lightwave]

This looks like what I was looking for will just have to convert to MS Access

http://sqlfiddle.com/#!18/4d6b4/1

 

[Lightwave]

Another discussion that might be useful
https://stackoverflow.com/questions/4463116/use-access-sql-to-do-a-grouped-ranking

 

[plog]

Do this with a DCount (https://www.techonthenet.com/access/functions/domain/dcount.php). You just count the number of competitors that have a better time than them and add 1 (so the first person isn't 0):

FinishRank: DCount("[CompetitorID]", "YourTableNameHere", "[FinishSeconds]<" & [FinishSeconds]) + 1

 

[jdraw]

Mark,
Here's another link re ranking query with ties.

IF I recall correctly, Allen Browne has an article on Ranking.

 

[arnelgp]

this is a customized function to rank your runners.

Public Function fnRank(TableName As String, _
			CompetitorFieldName As String, _
			CompetitorName As String, _
			FinishTimeFieldName As String As Integer

	Dim db As DAO.Database
	Dim rs As DAO.Recordset
	Dim iCount As Integer
	Dim savedTime As Date

	TableName = "[" & TableName & "]"
	TableName = Replace(Replace(TableName, "[[", "["), "]]", "]")
	CompetitorFieldName = "[" & CompetitorFieldName & "]"
	CompetitorFieldName = Replace(Replace(CompetitorFieldName "[[", "["), "]]", "]")
	FinishTimeFieldName = "[" & FinishTimeFieldName & "]"
	FinishTimeFieldName = Replace(Replace(FinishTimeFieldName "[[", "["), "]]", "]")

	'open the recordset
	Set db = Currentdb
	Set rs = db.Openrecordset( _
		"Select " & CompetitorFieldName & "," &  FinishTimeFieldName & " " & _
		"From " & TableName & " Order By " & FinishTimeFieldName & " Asc;")
	
	With rs
		If Not (.BOF And .EOF) Then 
			.MoveFirst
			.FindFirst CompetitorFieldName & "=" & CompetitorName
		End If
		While Not .BOF
			IF savedTime <> .Fields(FinishTimeFieldName).Value
				iCount = iCount + 1
				savedTime = .Fields(FinishTimeFieldName).Value
			End If
			.MovePrevious
		Wend
		.Close
	End With
	Set rs = Nothing
	Set db = Nothing
	fnRank = iCount
End Function

your query should look like:

Select fnRank("yourTable","theActualCompetitorFieldName", [theActualCompetitorFieldName], "TimeMarkFieldName") As Rank, 
	theActualCompetitorFieldName, TimeMrkFieldName From yourTable Order By 1;

 

[isladogs]

Yet another link. This one uses the Serialize function to determine rank order
http://www.mendipdatasystems.co.uk/rank-order-queries/4594424063

You're spoilt for choice !

 

[Lightwave]

Thanks guy - that counting everyone that is lower sounds so deceptively simple wonder why I couldn't have thought of it myself...!