Bitting off more than I can chew

  • Thread starter Thread starter Kenneth John Howie
  • Start date Start date
K

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.
 
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
 
Back
Top