Separating Lap Times

[paulsimo]

Could anybody please help me with a problem I have come across.
I am currently working on a project database to distinguish lap times from a bunch of athletic competitors. The database includes 2 tables, namely, Competitors and LapTimes. In the LapTimes table there are 2 fields which simply collect the competitors race number and the time they were recorded at the end of each lap, this is done automatically as they pass the finish line at the end of each lap.
With the query I have at the moment, named Laps it lists each competitor and each time they have recorded on a separate row. In the example database each competitor has completed 4 laps, so they are listed 4 times with each of their relevant lap times. Is it possible to separate each of the laps into different queries for each lap, so that it would be possible to view all competitors and times for lap 1 in one query, competitors and times for lap 2 in another query, and so on for each lap.
The number of laps may vary for different races.

I had thought of a query that would add a field named LapCount with a number 1 entered if the race number has not yet been recorded in the LapTimes table, a number 2 if it is already in the list once, a number three if it is already in the list twice, and so on. This would then enable me to distinguish separate laps in different queries.
However I am not sure if this is possible or how to achieve it. I would be very grateful if anybody could help me to overcome this problem. I have attatched an example of the database I am working on.

 

[jal]

Is it possible to separate each of the laps into different queries for each lap, so that it would be possible to view all competitors and times for lap 1 in one query, competitors and times for lap 2 in another query, and so on for each lap.

Perhaps you need to assign an ordinal to each lap time, meaning, that if a guy has finished 4 laps, they would be numbered 1, 2, 3, 4. Then a user could type into a textbox (txtLapNumber) either 1, 2, 3, or 4 and the query would then pull all the data for that lap number, like this:

SELECT * FROM LapTimes
WHERE LapNumber = Forms!myForm!txtLapNuimber

The question is how to assign ordinals to the lap times. The simplest way, conceptually, is to loop through a recordset, building your LapNumber column.

 

[paulsimo]

Yes, that is what I thought, I need to have some way of building the Lap Number column, this has to be done automatically without a user having to type in each lap number.
I had thought of using something like an IF THEN statement, stating :-

IF RaceNo = RaceNo AND Time > (previous) Time THEN LapNumber = LapNumber+1

Obviously the statement above, as it is, wouldn't work but something along those lines. The problem I see is how to get the query to check the previous times and from the resulting list, add the necessary lap numbers.

 

[jal]

I think I've seen queries that number the rows, but I can't recall at the moment. I take it you don't want to use a VBA recordset?

 

[jal]

I looked in my notes under "Ranking queries" and found this:

SELECT L1.*, (SELECT Count(L2.RaceNo) from LapTimes as L2
WHERE L1.RaceNo = L2.RaceNo AND L1.Time> L2.Time) + 1 as LapNumber
from LapTimes as L1

 

[jal]

I'm assuming, though, that the "date" portion of the DateTime field is actually stored. (In your upload, only the Time portion is visible). Because the condition

L1.Time> L2.Time

will fail if the times fall on either side of midnight, unless the date portion is stored as well.

 

[gemma-the-husky]

the biggest problem is finding the time to the end of the previous lap

personally i would actually enter the lap time ofr each competitor (not the cumulative), or enter both the total time for this lap, and the one before - so you DONT have to try and find the previous lap

 

[paulsimo]

Many thanks Jal
That is exactly what I was looking for, it works perfectly.
As regards the "date" portion of the datetime field I don't actually have that stored within the database, I can't ever see it being a problem as I don't expect any of the race times would fall either side of midnight, although to be safe I think I will add the date portion.

I am extremely grateful for your time and effort in obtaining the answer for me. Thanks once again.