Finding a minimum time for a swimming event

[jenascott]

That works for the date, time and venue
however it doesnt give me all the events

 

[davewhite04]

We'll get there try this:

SELECT Member.Forename, Member.Surname, Event.Gender AS Gender, Event.Age AS Age, Event.Distance AS Distance, Event.Stroke AS Stroke, Venue.[Name of Pool] AS VenueName, Times.[Date] AS DateofEvent, Times.Time AS TimeRecorded
FROM Member, Event, Times, Venue
WHERE Member.[Member ID]=Times.[Member ID] And Event.[Event ID]=Times.[Event ID] And Venue.[Venue ID]=Times.[Venue ID]
AND Times.Time = (SELECT Min(Times.[Time]) FROM Times WHERE Times.[Member ID] = Member.[Member ID] AND Event.[Event ID]=Times.[Event ID])
GROUP BY Member.Forename, Member.Surname, Times.[Date], Times.[Time], Venue.[Name of Pool],Event.Stroke,Event.Gender,Event.Age,Event.Distance;

 

[davewhite04]

For some reason the web form is putting a space between the c and e at the end of the statement above. Instead of "Distanc e;" It should read "Distance;"

 

[jenascott]

Thanks a lot
that seems to work at the moment, i will try it out with lots of different data later
Could you explain to me what you did to get this to work please? as i need to do a similar thing to get club records, and would like to have a go myself

thanks again jen

 

[davewhite04]

The problem you had was that adding the Date and Name of Pool cancelled out your "Min" function. I had to use what you call a "sub select" statement. At the beginning you had a "select statement" now you have two select statements in one query. The sub select statement here:

AND Times.Time = (SELECT Min(Times.[Time]) FROM Times WHERE Times.[Member ID] = Member.[Member ID] AND Event.[Event ID]=Times.[Event ID])

Solved your problem by just getting the best time(min(times.[time])) and using "joins" to make sure that the select statement just received what you wanted.

If I added the Date to the sub select we would have had the same problem.

Difficult to explain, it might be worth reading up a little on sub select statements.

 

[jenascott]

Any suggestions where i can reed up on sub select statements?

 

[davewhite04]

The best thing to do is make a database from scratch add a few tables and a bit of data, then mess about with queries using the working code above. access help isn't much use. I can't think of a book that covers this, but no doubt there will be one.

 

[jenascott]

Ok thanks
I will see what i can do with my other queries, and if i cant manage i will come back on here for help.
Thanks a lot for your help
jen

 

[davewhite04]

No problem.