Calculate places of a swimmer using times

[Guest]

How can one calculate te best, second best, third best ect, places in a query
using the swimmer time and then attach a value to the place to determine the
pionts of each swimmer during the meeting [Ex fastest time = place 1 - place
1 +11 =12 points, second fastest time = place 2 - place 2 + 8 = 120points,
ext, applicable to all the times from fastest to slowest of a itemnumber]

Thanks

 

[Guest]

I used the two tables below and the two queries. LAPTIME would be in seconds.

Table Frank_Swim ---
SwimmerID
LAPTIME

Table Frank_Swim_Score ---
Score
Rank

Query Frank_Swim_Rank ----
SELECT T.SwimmerID, T.LAPTIME, (SELECT COUNT(*)
FROM [Frank_Swim] T1
WHERE T1.LAPTIME <= T.LAPTIME) AS Rank
FROM Frank_Swim AS T
ORDER BY T.LAPTIME;

SELECT Frank_Swim_Rank.SwimmerID, Frank_Swim_Rank.LAPTIME,
Frank_Swim_Rank.Rank, Frank_Swim_Score.Score
FROM Frank_Swim_Rank INNER JOIN Frank_Swim_Score ON Frank_Swim_Rank.Rank =
Frank_Swim_Score.Rank
ORDER BY Frank_Swim_Rank.Rank;

 

[Guest]

I appreciate your quick response and help. I'll have to redesign my database
and try again.

Thank you very much

I used the two tables below and the two queries. LAPTIME would be in seconds.

Table Frank_Swim ---
SwimmerID
LAPTIME

Table Frank_Swim_Score ---
Score
Rank

Query Frank_Swim_Rank ----
SELECT T.SwimmerID, T.LAPTIME, (SELECT COUNT(*)
FROM [Frank_Swim] T1
WHERE T1.LAPTIME <= T.LAPTIME) AS Rank
FROM Frank_Swim AS T
ORDER BY T.LAPTIME;

SELECT Frank_Swim_Rank.SwimmerID, Frank_Swim_Rank.LAPTIME,
Frank_Swim_Rank.Rank, Frank_Swim_Score.Score
FROM Frank_Swim_Rank INNER JOIN Frank_Swim_Score ON Frank_Swim_Rank.Rank =
Frank_Swim_Score.Rank
ORDER BY Frank_Swim_Rank.Rank;

How can one calculate te best, second best, third best ect, places in a query
using the swimmer time and then attach a value to the place to determine the
pionts of each swimmer during the meeting [Ex fastest time = place 1 - place
1 +11 =12 points, second fastest time = place 2 - place 2 + 8 = 120points,
ext, applicable to all the times from fastest to slowest of a itemnumber]

Thanks

 

[David F Cox]

I have Trace as my race table, and t_points as the rank to points lookup
table. tested and works with my kludged data under Access 2007

q_rank:
SELECT Trace.swimmer, 1-Sum([trace].[timesecs]>[trace_1].[timesecs]) AS
rank, Trace.timesecs
FROM Trace, Trace AS Trace_1
GROUP BY Trace.swimmer, Trace.timesecs;


SELECT q_rank.rank, q_rank.swimmer, q_rank.timesecs, t_points.points
FROM q_rank INNER JOIN t_points ON q_rank.rank = t_points.rank
ORDER BY q_rank.rank, q_rank.swimmer;