Re: time calulations

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 02/29/04 

Date: Sun, 29 Feb 2004 14:37:40 +0800

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

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Kenneth John Howie" <kenjohn@ihug.co.nz> wrote in message
news:c1ruqj$44o$1@lust.ihug.co.nz...
> 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 spread***, 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.