Help!! :)

[jenascott]

Hi
I have a database that has tables with swimmers times for each event
how do i create a query to only show swimmers best times for each event rather than all the times they have ever achieved??
thanks
jen

 

[ScottGem]

Hi
I have a database that has tables with swimmers times for each event
how do i create a query to only show swimmers best times for each event rather than all the times they have ever achieved??
thanks
jen

Try using a Group BY query with a MIN for their time

 

[jenascott]

I tried that but it still shows swimmers slower times for the same event
Do you have any other suggestions??

 

[ScottGem]

You need to play with your groupings, but it should work.

 

[jenascott]

Sorry im really thick at this stuff!!!
Basically this is what i want to do
i have data like this:
jennifer.............froncrawl..........50m..............01.10.45
jennifer..............froncrawl..........50m..............01.09.34
Andrew..............froncrawl..........50m..............01.02.00
Andrew..............froncrawl..........50m..............01.03.66
and so on including other members
i want to produce a query that will only come up with only
jennifer..............froncrawl..........50m..............01.09.34
Andrew..............froncrawl..........50m..............01.02.00

Any suggestions?

 

[ScottGem]

You should be grouping by the first 2 columns and use Min for the third column. Can you post your SQL?

 

[jenascott]

SELECT Member.Forname, Member.Surname, Strokes.Name AS Strokes_Name, Distance.Distance, Age.Age, Gender.Gender, Times.Date, Venues.Name AS Venues_Name, Min(Times.Time) AS MinOfTime
FROM Venues INNER JOIN (Member INNER JOIN (Strokes INNER JOIN (Gender INNER JOIN ((Distance INNER JOIN (Age INNER JOIN Events ON Age.[Age ID] = Events.[Age ID]) ON Distance.[Distance ID] = Events.[Distance ID]) INNER JOIN Times ON Events.[Event ID] = Times.[Event ID]) ON Gender.[Gender ID] = Events.[Gender ID]) ON Strokes.[Stroke ID] = Events.[Stroke ID]) ON Member.[Member ID] = Times.[Member ID]) ON Venues.[Venue ID] = Times.[Venue ID]
GROUP BY Member.Forname, Member.Surname, Strokes.Name, Distance.Distance, Age.Age, Gender.Gender, Times.Date, Venues.Name;

 

[ScottGem]

I think you have too many fields in here to make it work. Try just using the minimum necessary to get the memberID and timeID of the min time. you canthen use that in a join to get the rest.

 

[jenascott]

Thanks,
I have done a query using just member id, event id and time and managed to get the fastest time only to appear
How do i then join it to get the members actual name and details of the event (stroke, distance, age group,m of f)?

 

[ScottGem]

Looking at your query, you might need to work this thru several querys. You may need to create a query that brings all the Event info you need joined on EventID and another with all the members info you need around MemberID. You then join all three of these queries around the fastest time query.

 

[jenascott]

Thanks for all your help
ive managed to get it to work apart from one thing
When i add in the date the time was acheved to the query, It causes all the times to appear again rather than just the minimum ones.
Any ideas what i am doing wrong??

 

[ScottGem]

What is your table structure?

 

[jenascott]

My tables are:
member details
Times - (where times for events are recorded)
Venues - (linked to times so venues where time is achieved can be stored)
Events - (this is linked through ids to the tables below)
Stroke
Distance
Gender
Age

 

[ScottGem]

I need the field list for at least Events and Time. Or you could just zip your database and attach it.