Finding a minimum time for a swimming event

[jenascott]

I am trying to produce a database that will select a swimmers, best time for each event, and will select the overall best time done by any swimmer in the events.

At the moment I am having some trouble with my queries.

When trying to get the query to select a swimmers best time for the events, It will work when selecting min for the time field and including the following fields:
Forename
Surname
Stroke
Distance
Age group
Gender

However I also wish to include the name of the swimming pool the time was done at and the date at which it was achieved. My problem is that when I add these fields into the query, it looks for the swimmers minimum time in the event, on the date, and at the venue and consequently comes up with all the times the swimmer has done, rather than just the best ones.

I would Really appreciated if anyone had any suggestions as to how I can get over this problem

Thanks in advance

Jennifer

 

[jenascott]

Please could someone reply with some advice
thanks
jen

 

[davewhite04]

Could you paste your current select statement then I might be able to help from there.

 

[jenascott]

Could you paste your current select statement then I might be able to help from there.

whats that??

 

[davewhite04]

In the query edit window there is a button on the upper left that you can click to run the query or you can select SQL and this gives you the SQL(Structered Query Language) statement.

 

[jenascott]

SELECT Member.Forename, Member.Surname, Event.Gender, Event.Age, Event.Distance, Event.Stroke, Min(Times.Time) AS MinOfTime, Times.Date, Venue.[Name of Pool]
FROM Venue INNER JOIN (Member INNER JOIN (Event INNER JOIN Times ON Event.[Event ID] = Times.[Event ID]) ON Member.[Member ID] = Times.[Member ID]) ON Venue.[Venue ID] = Times.[Venue ID]
GROUP BY Member.Forename, Member.Surname, Event.Gender, Event.Age, Event.Distance, Event.Stroke, Times.Date, Venue.[Name of Pool];

 

[davewhite04]

Are you sure that is the correct SQL statement? According to that both the Age and Gender fields are in the Event table which seems odd to me.

Can you confirm.

 

[jenascott]

Are you sure that is the correct SQL statement? According to that both the Age and Gender fields are in the Event table which seems odd to me.

Can you confirm.

yes im sure,
the age is in the events table because you can have 7 years 50m freestyle male and 8 years 50m freestyle male, which are two different types of events

 

[davewhite04]

OK I understand now, give me a little while to figure this out.

 

[jenascott]

OK I understand now, give me a little while to figure this out.

ok thanks a lot

 

[davewhite04]

Try creating a new query and using the following SQL statement (just copy and paste into the SQL window)

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

Hope this helps.

 

[jenascott]

when i enter this the query asks me to enter a venue.eventID
?????????

 

[jenascott]

do you want me to send you my database to have a look at???

 

[davewhite04]

Try this:

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

 

[jenascott]

Its now asking me for a Member.MemberID

 

[davewhite04]

OK, I think I made one error, try this:

SELECT Member.Forename, Member.Surname, First(Event.Gender) AS Gender, First(Event.Age) AS Age, First(Event.Distance) AS Distance, First(Event.Stroke) AS Stroke, Venue.[Name of Pool] AS VenueName, First(Times.Date) AS DateofEvent, Min(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]
GROUP BY Member.Forename, Member.Surname, Venue.[Name of Pool];

 

[jenascott]

This query is producing the swimmers minimum time, however it is giving their best time at each venue rather than just their best time.

Also the date is selecting the first date rather than the date the time was done on.

 

[davewhite04]

Oh yes, a mistake on my part. I'm gonna have to think about this a bit more. I'll get back to you asap.

 

[jenascott]

Oh yes, a mistake on my part. I'm gonna have to think about this a bit more. I'll get back to you asap.

ok thanks a lot

 

[davewhite04]

Hopefully this will crack the problem fingers crossed:

SELECT Member.Forename, Member.Surname, First(Event.Gender) AS Gender, First(Event.Age) AS Age, First(Event.Distance) AS Distance, First(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])
GROUP BY Member.Forename, Member.Surname, Times.[Date],Times.[Time],Venue.[Name of Pool];