It works well now. The top 20 times is actually the bottom 20, so I
took a shot and changed the last line to "ORDER BY Q.EventTime ASC"
and got the expected results.
There is a small glitch that I noticed. If a swimmer had the same time
in two different years, then both records appear in the top 20. Is
there a way to edit the query so that they only appear once
(preferably the first time they achieved the time).
Thanks for the help.
~ Horatio
:
Forgot To Group By the Name. DOH!!!!
Query One (saved as qMinTimes:
SELECT H.Name, Min(H.[200 Free]) as EventTime
FROM Heptathlon as H
GROUP BY H.Name
Query Two:
SELECT Top 20 H.Name, Q.EventTime, H.Year
FROM Heptathlon as H INNER JOIN QMinTimes as Q
ON H.Name = Q.Name
AND H.[200 Free] = Q.EventTime
ORDER BY Q.EventTime DESC
Hopefully this will work.
As far as changing the structure. Yes, that would be a good idea,
but if you have imported this from a spreadsheet, it may not be
practical to do so.
If your structure were as I described, you could get the top 20 in
each event in one (slightly complicated) query. As it is now you
will need to run one query for each event type.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Horatio J. Bilge wrote:
It doesn't seem to work for me.
First I got an error message that says it can't find "QMinTimes." I
changed my field names to have no spaces, and changed Name to
SwimmerName, and Year to EventYear. I then changed the SQL you
suggested to match the changes, and got the same error.
Then I figured that QMinTimes must be the name of the first query,
so I changed the name to match, and now I get an error: "You tried
to execute a query that does not include the specified expression
'SwimmerName' as part of an aggregate function."
This is a sample record from my table with the fixed fields:
ID SwimmerName 200Free 200IM 50Free 100Fly
100Free 100Back 100Brst EventYear
1 Emma Wright 0.001533565 0.00172338 0.000326389
0.000864583 0.000707176 0.000837963 0.000914352 2006
As far as my table design, are you suggesting that I have a separate
record for every individual swim, instead of one record per swimmer
per year? The reason I have it laid out in the way I do is because I
imported it from an Excel worksheet that is laid out that way.
~ Horatio
:
Since your field names contains spaces you will have to do this
with two queries.
Query One:
SELECT H.Name, Min(H.[200 Free]) as EventTime
FROM Heptathlon as H
Query Two:
SELECT Top 20 H.Name, Q.EventTime, H.Year
FROM Heptathlon as H INNER JOIN QMinTimes as Q
ON H.Name = Q.Name
AND H.[200 Free] = Q.EventTime
ORDER BY Q.EventTime DESC
(If there are ties for the last position you will get more than 20
records.)
ALSO your table design would be better if you had just the fields
below.
ID
SwimmerName
Name is a property of every object in the database,
avoid it in naming fields)
EventYear : (Year is a function so avoid it in naming fields)
EventType: contains "200 Free", "100 back" etc,
SwimTime: (Time is a function, so avoid it in naming fields)
Also avoid spaces in field names.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Horatio J. Bilge wrote:
I had to do some searching to figure out what SQL was, but I found
it.
I created my queries with the wizard. The first one gives me each
swimmer's best time for the 200 Freestyle, but not the year. The
second one gives the year, but includes every time the swimmer
achieved (once each year). What I need is each swimmer's best
time, the year they swam that best time, and then select the top
20 from those. There will be seven of these (one for each event).
200FreeTopTimes:
SELECT DISTINCTROW Heptathlon.Name, Min(Heptathlon.[200 Free]) AS
[Min Of 200 Free]
FROM Heptathlon
GROUP BY Heptathlon.Name;
200FreeTopTimesYear:
SELECT DISTINCTROW Heptathlon.Name, Heptathlon.Year,
Min(Heptathlon.[200 Free]) AS [Min Of 200 Free]
FROM Heptathlon
GROUP BY Heptathlon.Name, Heptathlon.Year;
Sample Data. I included some example of swimmers with multiple
times (multiple years), and some with a single time (just one year):
ID Name 200 Free 200 IM 50 Free 100 Fly 100
Free 100 Back 100 Brst Year
25 Abby 0.00197338 0.002450231 0.000415509
0.001247685 0.000945602 0.001177083 0.001274306 2006
10 Anne 0.001693287 0.001956019 0.000351852
0.001002315 0.000789352 0.000975694 0.001037037 2006
33 Anne 0.001655093 0.001855324 0.000335648
0.00090162 0.00074537 0.000909722 0.000979167 2007
53 Anne 0.001491898 0.00171412 0.000318287
0.000822917 0.000672454 0.000821759 0.000896991 2008
13 Alex 0.001828704 0.002155093 0.000372685
0.001100694 0.000814815 0.000959491 0.001174769 2006
34 Alex 0.001760417 0.001921296 0.000340278
0.000944444 0.000765046 0.000891204 0.001060185 2007
59 Alex 0.001575231 0.001892361 0.000335648
0.000883102 0.000730324 0.000899306 0.001015046 2008
50 Bridgett 0.00209375 0.002305556 0.000391204
0.00112963 0.000918981 0.000987269 0.001195602 2007
52 Delaney 0.001465278 0.001666667 0.000315972
0.000813657 0.000678241 0.000771991 0.000876157 2008
1 Emma 0.001533565 0.00172338 0.000326389
0.000864583 0.000707176 0.000837963 0.000914352 2006
29 Emma 0.001512731 0.001719907 0.000331019
0.000777778 0.000716435 0.000815972 0.000922454 2007
57 Emma 0.001564815 0.001833333 0.000350694
0.000869213 0.000702546 0.00083912 0.000959491 2008
39 Johanna 0.001710648 0.002055556 0.000371528
0.000894676 0.000790509 0.000931713 0.001090278 2007
60 Johanna 0.00159838 0.001836806 0.00034838
0.000847222 0.000747685 0.000920139 0.001028935 2008
8 Kari 0.001627315 0.001914352 0.000355324
0.000967593 0.000763889 0.001030093 0.001021991 2006
36 Kari 0.001665509 0.001952546 0.000365741
0.000928241 0.000768519 0.001021991 0.001 2007
63 Kari 0.001684028 0.001972222 0.000368056
0.001016204 0.000746528 0.000982639 0.001042824 2008
31 Kate 0.001688657 0.001850694 0.000336806
0.000857639 0.000756944 0.000815972 0.000975694 2007
55 Kate 0.001541667 0.001697917 0.000320602
0.000824074 0.000706019 0.00077662 0.000907407 2008
4 Morgan 0.001552083 0.001842593 0.000340278
0.000861111 0.000747685 0.000789352 0.001045139 2006
32 Morgan 0.001606481 0.001827546 0.000340278
0.000907407 0.000728009 0.000793981 0.001 2007
14 Nicole 0.001802083 0.002072917 0.000383102
0.001131944 0.000851852 0.001012731 0.001030093 2006
44 Nicole 0.001828704 0.002075231 0.000377315
0.001049769 0.000854167 0.000944444 0.001055556 2007
68 Nicole 0.000385417 0.000871528
0.000994213 0.001024306 2008
66 Paige 0.001854167 0.001983796 0.000363426
0.00096875 0.000828704 0.000864583 2008
Thanks,
~ Horatio
:
Post your SQL and sample data.
--
KARL DEWEY
Build a little - Test a little
:
I am very new to using Access, so this may be a simple question.
I imported some excel worksheets (in .csv format) into a table
in Access. The table has columns for names, times for swimming
events, and year. To import correctly, I formatted the times as
"general" in Excel, so they are decimal values. For example, the
first record looks like this (there are seven events - I am just
showing three below):
Name 200 Freestyle 200 Ind Med 50 Freestyle Year
Andrea 0.001533565 0.00172338 0.000326389 2006
What I am trying to do is generate a Top20 list for each event.
The final result ideally would give the name, time, and year of
the top 20 swimmers:
Top 20 - 200 Freestyle
Emma 0.001509259 2008
Andrea 0.001533565 2006
etc.
I used a query with Name and Min Of 200 Freestyle time to find
each swimmer's fastest time. For example, Emma swam each event 3
times, but I just want her fastest time. The problem was
including the year - when I included the Year field, the result
included every time, not just the fastest time.
Can someone help me figure this out?
Thanks,
~ Horatio
