Solved Minimum Values From A Set of Records For Each Occurrence Then Create Those Results in Another Table (2 Viewers)

[Gimlett]

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

 

[MajP]

SQL GROUP BY Statement

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[ebs17]

Is it better to use a query or vba?

In a database, SQL is the native language.

This is the actual calculation:

SELECT
   RunnerRef,
   EventRef,
   MIN(TotalSeconds) AS MinTime
FROM
   TableX
GROUP BY
   RunnerRf,
   EventRef

In order to get the other fields of the data record available, you have to link the table again for this query:

SELECT
   T.*
FROM
   TableX AS T
      INNER JOIN
         (
            SELECT
               RunnerRef,
               EventRef,
               MIN(TotalSeconds) AS MinTime
            FROM
               TableX
            GROUP BY
               RunnerRf,
               EventRef
         ) AS S
         ON T.RunnerRef = S.RunnerRef
            AND
         T.EventRef = S.EventRef
            AND
         T.TotalSeconds = S.MinTime

 

[arnelgp]

SELECT Table1.*
FROM Table1
WHERE [h]*3600+[m]*60+[s]=DMin("[h]*3600+[m]*60+[s]","table1","RunnerRef=" & [RunnerRef] & " And EventType='TimeTrial'") AND (Table1.EventType)="TimeTrial";

 

[Gimlett]

Ebs17, sorry to be stupid but what does T represent in your example please?

 

[ebs17]

SQL Aliases

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[Gimlett]

TotalSeconds is a calculated field

SELECT
T.*
FROM
tblResults AS T
INNER JOIN
(
SELECT
RunnerRef,
EventRef,
MIN([h]*3600+[m]*60+=DMin("[h]*3600+[m]*60+) AS MinTime
FROM tblResults GROUP BY RunnerRef,EventRef) AS S ON T.RunnerRef = S.RunnerRef AND T.EventRef = S.EventRef AND T. MIN([h]*3600+[m]*60+=DMin("[h]*3600+[m]*60+) = S.MinTime

Access doesn't accept this. Is it best then if I store Totalseconds in the table? I thought I should use calulated fields whenever possible.

Thanks

 

[ebs17]

Access doesn't accept this

Why should results of free rhyming be accepted?

I thought I should use calulated fields whenever possible.

How do you come up with such thoughts? If you repeat your calculation expressions, they should also be executed repeatedly => continuous fire costs time. If you think in dimensions of a database: Calculated fields do not offer any use of a possibly existing index => wasted performance.

Is it best then if I store Totalseconds in the table?

Counter-questions: How are times determined before they are entered in the table? How are times later used?
The use and storage of separate hours, minutes and seconds is the most inefficient variant. Or is it that you want your computer to be busy and you need to kill time?

 

[MajP]

Access doesn't accept this

What you wrote is nothing like what either @arnelgp wrote or @ebs17. Look at the DMIN you wrote, that for sure is going nowhere fast.

 

[Gimlett]

Well you make some good points, even if I do feel like I'm getting beaten up.

I'm glad you like my rhyming, I'm probably a better artist than scientist as you may tell.

How I come up with such thoughts is a long and complicated story which I am describing in my current novel. I'll be sure to promote it so you can purchase a copy if I ever finish it. Suffice to say I shall revise my thinking regarding calculated fields and ponder the reasons as you so describe; thanks.

When you ask how are times determined, they are taken from a stopwatch as runners cross the finish line. I wonder are you suggesting I could use start time and end time as time of day to determine these values rather than the wasteful method of hours, minutes, seconds I am using?

Actually my computer is quite powerful, It has an I7 prcocessor that runs Snowrunner very smoothly but I take your point.

Thank you also Majp, back to the drawing board!

 

[MajP]

Can you either post sample data? Post the relevant tables by exporting into a new database. No need to post all the forms and reports and tables not used in the query.

 

[ebs17]

I'm glad you like my rhyming

On the contrary: I almost feel physical pain.

A time in a date field (#00:20:20#) or a time in seconds (1220) in a long field would be good table entries and starting points for the following.

 

[Gimlett]

Sorry to cause you pain ebs17, you may like to try some Keats if my poetry is not to your taste. Thank you very much for your help, I have made some progress now once I'd typed in the SQL you supplied me exactly as you'd supplied it and without my typos and errors.

MajP Thanks also,

I have enclosed the relevant parts of the database, please find it here and thank you for your time.

 

[arnelgp]

see Query1, is this what you need?

 

[Gimlett]

Hi Arnelgp, yes I think that's it, I wasn't sure at first but now I see every runner once and the event when they achieved their fastest time. That's fabulous thank you so much.

Homage to ebs17 and MajP also,

I think it was my poorly worded question that caused any confusion but thanks to you all for your patience and expertise.