Convert finishing time to position

[Mantaii]

Hi!
Is it possible within a query to convert a time to a finishing position for a specific month.



i.e.

I have a runner who run 00:35:00 for a race in May
I also have a running who runs the same race in May in 00:34:59

Is there a way to put a position next to their name automatically so the second runner with 00:34:59 is allocated position 1 and the other runner is allocated position 2 etc.

The reason I need to have a month and year is because the race is only ran once a month but I want to be able to print of reports for any year and month which will be stored in the database.

Thanks,

[Robeen]

I know you said 'query' - but - would a report work for you?

In a report you can not only Group records by Month - but you can also create a 'Running Count' field that will give you your positions.

If you Group your runs by Month and then sort by Timing [Ascending] - then the very first timing [the 'smallest' time] will have a running count [Position] of 1.

I don't know if there is a way to do a running count in a query.
If there is - I'd like to know it - because I'll probably find a use for it one of these days!

In a query - the best I can think of right now is to sort by Month and then by Timing.
You won't get the positions per se - but the fastest for each month will be the first one on the list for that month.

I hope this helps.

[Robeen]

I did a quick foray into 'Googledom' and found no query that would give you the position for each runner for each month.

Another option that I would consider if it was something I was doing would be to write up a quick function with VBA to create the positions in each row of your data.
Are you comfortable writing VBA code?

If you MUST have a query then I am out of ideas.

[Mantaii]

Hey, thanks for the replies. It doesn't need to be a query, it could be a report. Its been quite some time since I worked with access so getting my head around it again is taking a bit of working out.
I have done VBA coding as well but again, its been a while. Comfortable editing someone elses code to make it work for me but writing from scratch is another thing.

Will look at reports again and see if I can get what you suggested.

Thanks again!

[ssanfu]

Hi!
Is it possible within a query to convert a time to a finishing position for a specific month.

Have you tried Googling "ranking query"? There are lots of posts on this since it is a common question.

The ranking is determined by counting records outside some threshold. Here is a sample:

SELECT
p.KeyPct
, (SELECT COUNT(*) FROM tblMiscPct WHERE KeyPct >= p.KeyPct) AS Rank
FROM tblMiscPct AS p
ORDER BY p.KeyPct DESC;

Here "KeyPct" determines the order, highest value first. The second field ("Rank") is a
subquery count of records that equal or exceed the current record's value (resulting in rank).

*** And another method:

First you have to create a module then paste this code to that new
module

Option Compare Database
Dim CustID As Integer
Dim ctr As Integer

Function R(Id As Integer, intVal As Integer) As Integer
If CustID <> Id Then
ctr = 1
CustID = Id
Else
ctr = ctr + 1
End If
R = ctr
End Function

Then use this query

SELECT Territory, Potential,R(Territory, Potential) as rank
FROM Table1
ORDER BY Territory, Potential DESC;

------------------------------------
These two examples are from this post:

http://bytes.com/topic/access/answer...-records-query

[Mantaii]

Thanks! I went down the report option as it sorted everything perfectly. On with the next stage now and no-doubt I will be back looking for more answers. The problem I tend to find it not know what to google for.