time calulations

[Kenneth John Howie]

I'm building bike racing database that uses times [hh:nn:ss] and I have
managed to make all the queries work, but I'm not shore how to do this last
part of the query.
In a spreadsheet, you would take the shortest time from the next time and
pull that down the cells so each would see there time differences from the
race leader.
time of 07:16:20 race
leader
07:16:30 00:00:10 =A6-A5 second place
07:16:56 00:00:36 =A7-A5 third place
07:16:59 00:00:59 =A8-A5 forth place

does this make any sence to use out there, if so can someone set me on the
right path.. I can attach or send an excel file, is size is 290kb.

Ken Howie

 

[Allen Browne]

Use a subquery to get the shortest time for the race, and subtract it from
the time in this record.

Assuming a table named tblRaceTime where you enter all the times for the
races (with a foreign key RaceID to identify which race this entry if for),
you could get the fastest time for the race by entering this into the Field
row of your query:
WinnerTime: (SELECT Min([RaceTime]) FROM tblRaceTime AS Dupe
WHERE Dupe.RaceID = tblRaceTime.RaceID)

Therefore the calculated field to show the number of seconds would be:
Seconds: DateDiff("s", (SELECT Min([RaceTime])
FROM tblRaceTime AS Dupe
WHERE Dupe.RaceID = tblRaceTime.RaceID), [RaceTime])