Bitting off more than I can chew

[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

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])

Thankyou for your help but I must be a bit thick as I have tryed different
combinations using my query names but I still cannot get it to work,


A Prologue Entry_ID GradeID Race Number_ID Riders Name Placing_ID Actual
TT start time_ID Line Time Prologue Result_ID RaceID
1 A Grade 1 Tim Carswell 1 00:01:00 00:05:23 00:04:23

34
2 John Smith 2 00:02:00 00:06:25 00:04:25 00:00:02

35
3 Paul Howie 3 00:03:00 00:07:36 00:04:36 00:00:13

36
4 peter cow 4 00:04:00 00:08:45 00:04:36 00:00:13

37
5 Rodger Pool 5 00:05:00 00:09:56 00:04:56 00:00:33



the result in RaceID is what I want but I get blank cell, I had to manually
input these in this email.

 

[Gerald Stanley]

Try something along the following lines

SELECT R1.raceId, R1.raceTime, DateDiff("s", R1.leaderTime,
R2.racetime)
FROM tblRaceTime R1,(SELECT raceId, Min(raceTime) As
LeaderTime FROM tblRaceTime GROUP BY raceId) R2
WHERE R1.raceId = R2.raceId

Hope This Helps
Gerald Stanley MCSD