I would like to determine the minimum time as personal best for each runner from a number of events. I would then like to pick out this value to provide a subset of data only displaying the runners and their fastest time.
Is it better to use a query or vba? I’m struggling with both. If I use the visual query builder I get confused with totals as in min or count when using expressions rather than actual fields.
Access built this sql for me but the PB expression doesn’t return the smallest value each time. I need to compare totalseconds for each runner in multiple records (events) for each runner then display the min of those for each runner. So one occurrence of a runner with their fastest time (PB).
SELECT tblRunners.RunnerRef, tblRunners.ClubRef, tblResults.EventRef, tblResults.H, tblResults.M, tblResults.S, tblResults.Handicap, tblResults.NewHandicap, tblEvents.EventType, (([h]*3600+[m]*60+)) AS TotalSeconds, Min([TotalSeconds]) AS PB FROM tblRunners INNER JOIN (tblEvents INNER JOIN tblResults ON tblEvents.EventRef = tblResults.EventRef) ON tblRunners.RunnerRef = tblResults.RunnerRef GROUP BY tblRunners.RunnerRef, tblRunners.ClubRef, tblResults.EventRef, tblResults.H, tblResults.M, tblResults.S, tblResults.Handicap, tblResults.NewHandicap, tblEvents.EventType HAVING (((tblRunners.ClubRef)=10) AND ((tblEvents.EventType)="TimeTrial"));
Ideally I want the minimum value in the PB column on the right and eventually just the PB for each runner and that list of runners and PBs. I then want to work with that dataset to apply the handicap in the appropriate columns.
I think you may wonder why I split the times into h,m,s but this seems to work ok and I don’t understand how to calculate time as Access keeps trying to turn it into a format I don’t want such as the twenty four hour clock.
I realise I’m asking a lot and wonder can someone point me to a similar existing thread I can convert to my situation?
Thanks
Chris
Is it better to use a query or vba? I’m struggling with both. If I use the visual query builder I get confused with totals as in min or count when using expressions rather than actual fields.
Access built this sql for me but the PB expression doesn’t return the smallest value each time. I need to compare totalseconds for each runner in multiple records (events) for each runner then display the min of those for each runner. So one occurrence of a runner with their fastest time (PB).
SELECT tblRunners.RunnerRef, tblRunners.ClubRef, tblResults.EventRef, tblResults.H, tblResults.M, tblResults.S, tblResults.Handicap, tblResults.NewHandicap, tblEvents.EventType, (([h]*3600+[m]*60+)) AS TotalSeconds, Min([TotalSeconds]) AS PB FROM tblRunners INNER JOIN (tblEvents INNER JOIN tblResults ON tblEvents.EventRef = tblResults.EventRef) ON tblRunners.RunnerRef = tblResults.RunnerRef GROUP BY tblRunners.RunnerRef, tblRunners.ClubRef, tblResults.EventRef, tblResults.H, tblResults.M, tblResults.S, tblResults.Handicap, tblResults.NewHandicap, tblEvents.EventType HAVING (((tblRunners.ClubRef)=10) AND ((tblEvents.EventType)="TimeTrial"));
Ideally I want the minimum value in the PB column on the right and eventually just the PB for each runner and that list of runners and PBs. I then want to work with that dataset to apply the handicap in the appropriate columns.
I think you may wonder why I split the times into h,m,s but this seems to work ok and I don’t understand how to calculate time as Access keeps trying to turn it into a format I don’t want such as the twenty four hour clock.
I realise I’m asking a lot and wonder can someone point me to a similar existing thread I can convert to my situation?
Thanks
Chris
Last edited: