2. ASPRunnerPro General questions
  3. Ranking of time fields
This topic is locked

Ranking of time fields

 		3/2/2007 04:00:46
ASPRunnerPro General questions
G
 grinningdog author

I've finally got my time fields sorted out but what I want to do now is to rank them and put the result into another field.
The idea is that the shortest time, say 00:01:01, will get a ranking of 1 and 00:01:02 would get 2. A time of 04:13:23 would get a ranking of 250 say. There are four of these ranking fields to populate.
I think it's better to do this as a one off operation at the end rather than try to do it on the fly and alter each ranking every time I enter a new time.
Therefore I'm thinking along the lines of a separate table view but all I really need is a big button marked "rank all entries". Maybe a good approach would be a page to check for any missing time entries, correct any missing times and then hit the ranking button.
Do you think this would be a better plan than changing the ranking on the fly after each entry? Bearing in mind the complications of time fields (!) is this a feasible idea?
Bob
<later> Another possiblity I've just thought of would be to sort by the relevant column and then have button to populate the field in that order. Is that better/possible?
J
 		Jane 3/2/2007

Bob,
the one of the easiest way to rank all entries is to use ListOnLoad event.

Here is a sample:

Sub ListOnLoad()

str = "select * from " & strTableName &" order by TimeField asc"
Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection

ii=1

while not rsTemp.eof

strUpdate = "update " & strTableName & " set RankField=" & ii & " where IDField="&rsTemp("IDField")

dbConnection.Execute strUpdate

ii = ii+1

rsTemp.movenext

wend

rsTemp.Close : set rsTemp = Nothing
End Sub
G
 		grinningdog author 3/2/2007

Actually that answers another question (which I would have got to <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=15951&image=1&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' /> ) but... doesn't it just list the infomation in a specific order?
I was looking to have a field with a ranking so I can show my list as something like this:
Name Time1 Ranking2 Time2 Ranking2 Time3 Ranking3 Total Final Ranking
Fred 00:08:00 1 00:60:00 2 00:29:00 2 01:37:00 2
Joe 00:09:00 2 00:59:00 1 00:28:00 1 01:36:00 1
If that's easy to achieve, great. If not I'll fall back on making the users re-sort manually to see the results they want.
Bob
Sergey Kornilov admin 3/2/2007

The code that Jane posted updates field named RankField with correct value everytime List Page is loaded.
This solution is not the smartest one however it works.
Alternatively you need to do the same reordering after each edit/add/delete.
G
 		grinningdog author 3/2/2007

Excellent. Thanks for the clarification. I was obviously confused by "order by TimeField asc" and didn't think it through.
Bob
G
 		grinningdog author 3/5/2007

Hi Jane
I keep getting an error - [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
Can you just expand on this part for me because I think that's where it's going wrong

" where IDField="&rsTemp("IDField")


Bob
Sergey Kornilov admin 3/5/2007

In this example IDField is the name of ID field in your table.

Replace it with the actual field name.
G
 		grinningdog author 3/5/2007

Yep, that's what I thought.
I still get an error

Error number -2147217900

Error description [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
Here's my actual code

Sub ListOnLoad()

str = "select * from " & strTblIndividual &" order by Swim asc"
Set rsTemp = server.CreateObject("ADODB.Recordset")

rsTemp.open str, dbConnection

ii=1

while not rsTemp.eof

strUpdate = "update " & strTblIndividual & " set SwimRank=" & ii & " where ID="&rsTemp("ID")

dbConnection.Execute strUpdate

ii = ii+1

rsTemp.movenext

wend

rsTemp.Close : set rsTemp = Nothing
End Sub


Where TblIndividual is my table, Swim is the field with the times in it, SwimRank would hold the calculated number and ID is the autonumber field for unique to each record. I've gone cross eyed looking at it and trying alternatives.
Bob
Sergey Kornilov admin 3/5/2007

Don't replace strTableName with strTblIndividual.