Ok, this may be a case where two queries are better than one. But a problem
arises - let's say that a swimmer has the same minimum time when they are 9 and
when they are 10. You will get two records back in the case of ties like that.
Also, a fix for the name issue would be to use First against the the name field.
SELECT SwimmerID, First([Name]) as SwimmersName, LSC,
Sex, Distance, Stroke, Course_Code, Min([Time]) as FastestTime
FROM Swims
WHERE LSC='OH'
AND Age <= 10
AND Sex='M'
AND Distance='50'
AND Stroke='Free'
AND Course_Code='Long'
GROUP BY SwimmerID, LSC, Sex, Distance, Stroke, Course_Code
Save that as queryA (or whatever name you want) and then use that in another query.
SELECT Q.SwimmerID, Q.SwimmersName, Q.LSC, Q.Sex, Q.Distance
Q.Stroke, Q.CourseCode, FastestTime, S.Age
FROM QueryA as Q INNER JOIN Swims as S
ON Q.SwimmerID = S.SwimmerID AND
Q.FastestTime = S.[Time] AND
Q.LSC = S.LSC AND
Q.Distance = S.Distance AND
Q.Stroke = S.Stroke AND
Q.Course_Code = S.Course_Code AND
Q.Sex = S.Sex
Some of the join fields are probably unneeded (such as Q.Sex = S.Sex)
Well, in my opinion the standards committee is making a major error as far as a
relational database is concerned. If they need the output to be in one file,
that can be accomplished in a query joining the tables as needed.
Anyway, I hope the above will work for you.
OK - We're 95% there.
I think the age thing needs some explination. The Max(age) and Min(age)
columns are giving the youngest and oldest age that the swimmer was during
the season. Since everyone has a birthday during the season, it's giving me
their age before their birthday and after their birthday. (I can't group by
age becuase then it would think it was 2 seperate swimmers.) I want the age
they were when they swam the Min(Time).
Would it be easier just to get the SwimID of the Min(Time) and then lookup
the information in a subquery once I have that SwimID? Is there a way to do
that?
Also - I know about the design stuff - but unfortunately, I don't get to
change the design. It is designed by a standards committee and all the stuff
is supposed to be in one table. The name thing is giving me problems, but
there is not a whole lot I can do about that, and they know it. I'm trying
to get them to change it.
Cpierswim
:
So add in the swimmerID and group by the swimmerID, if that is consistent for an
individual swimmer
SELECT SwimmerID, LSC, Min(Age) as YoungestAge, Max(Age) as OldestAge,
Sex, Distance, Stroke, Course_Code, Min([Time]) as FastestTime
FROM Swims
WHERE LSC='OH'
AND Age <= 10
AND Sex='M'
AND Distance='50'
AND Stroke='Free'
AND Course_Code='Long'
GROUP BY SwimmerID, LSC, Sex, Distance, Stroke, Course_Code
By the way, good design would have a table of Swimmers with the SwimmerId, Name,
Sex, Birthday, and other items relevant to the swimmer and not to the Swim.
In the Swims table, you would only have the SwimmerID and the data relevant to
the Swim.
You might also have another table of Meets with the data relevant to the meet.
In that case, you would include the MEET_ID in the Swims table. Right now, you
can get two records for the same person if you have managed to misspell their
name and wanted the name included in the query results.
Cpierswim wrote:
No - that doesn't work. It gives me only the fastest time out of everyone in
that event (the result in always 1 record). I want the fastest time of each
and every swimmer that meets that selection criteria. (hundreds of records)
I want the resulting table to be the exact same as the original table, only
removing all swims that do not meet the selection criteria and also only
displays the fastest time for each swimmer (if they have more than one swim
that qualifies in the selection criteria).
Cpierswim
:
SQL statement should look something like
SELECT LSC, Min(Age) as YoungestAge, Max(Age) as OldestAge,
Sex, Distance, Stroke, Course_Code, Min([Time]) as FastestTime
FROM Swims
WHERE LSC='OH'
AND Age <= 10
AND Sex='M'
AND Distance='50'
AND Stroke='Free'
AND Course_Code='Long'
GROUP BY LSC, Sex, Distance, Stroke, Course_Code
I included two ages since you specified <= 10 as the Age which gives you a range
of ages. If you wanted the best time at different ages then Group By Age and
drop the Min and max and just put Age in the field.
If you are using the query grid to build your queries,
-Build the query with the fields you want
-Select View: Totals from the menu
-Change Group by under time to Min
-If you want the age ranges put the age in twice and change one to Min and the
other to Max.
Cpierswim wrote:
I'm a swim coach and I have a huge database of times. I'm trying to get the
database to display the best time of each swimmer in order from fastest to
slowest. Here are the column's in my table
Table: Swims - a table of individual swims for a swimmer over a year. Each
swimmer can have hundreds of swims
-ID_Number - The Numeric key identifier for each swim
-Swimmer_ID - The identifier for the swimmer - there are many duplicates
-Name - The swimmer's name
-Team - The swimmer's team
-LSC - The swimmer's state (I'm only ranking swimmers in a certain state at
this point)
-Age - The swimmer's age on date of swim
-Sex - The swimmer's gender
-Birthday - The swimmer's birthday
-Distance - The length of the swim
-Stroke - The type of swim (freestyle, backstroke...)
-Date_Of_Swim - The day this swim was swum
-Time - The time of the swim
-Course_Code - The type of pool the swim was swum in (Long course, short
course)
-Meet_ID - the meet identifier that the swim was swum in
I want to create a query that will have the following properties
LSC='OH', Age= Less or equal to 10, Sex='M', Distance='50', Stroke='Free',
Course_Code='Long'
I know how to create a query that will give me this, but it lists every
single swim that a swimmer swam that falls into this category. I only want
the Record displayed that is each swimmer's best time ( min([Time]) )
So what is created is a list ranked from fastest to slowest of all the
Swimmer's best times in this event (set of properties).
Suggestions? Requets for clarifications? Please help.
Cpierswim
