10 Fastest Swimmers

[Pete]

I have a query which I would like to display the 10
quickest swimmers. How can I limit the query to show only
10 records.

Secondly, 1 swimmer may have many times for an event, but
I only want the quickest time to display in the above
question.

Pete

 

[Andreas]

Use a summary totals query to find the Min value for each swimmer (Sigma
button on toolbar, Group By swimmer, Min for time).
Use the Top Values option (on toolbar) to limit the number of records shown.
Make sure you sort by times (ascending).

Regards,
Andreas

 

[Pete]

how do I only display the quickest time if a swimmer has
two or more records in the query.

e.g

Jim Smith 1.23.45
Jim Smith 1.24.56

I would only want to see the quickest time

Hope you can help

Pete

 

[Douglas J. Steele]

The solution Andreas posted will do that: note the mention of grouping by
swimmer.

 

[Pete]

Hi Doug,

I'm still getting a swimmer with 2 records. I must be
doing something wrong.

I've clicked the AutoSum button to get the GroupBy up,
selected Min for the Time and sorted Ascending

How do I GroupBy swimmer, it displays the words "GoupBy"
under the SwimmerID filed, but the 1st two times
displayed are from the same swimmer.

I'm a bit of a newbie, can you point me in the right
direction

Pete

 

[Ken Snell [MVP]]

Open the query in design view, click on the "Query view" icon on toolbar,
select SQL view, and copy the entire statement that you'll see in that
window.

Paste it into a reply to this message so that we can see what the query is
that you're trying to use.

 

[Guest]

SELECT DISTINCT TOP 10 tblSwimmersTimes.SwimmerID, Min
(tblSwimmersTimes.PB) AS MinOfPB,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN
(tblStroke INNER JOIN (tblLevel INNER JOIN (tblDistance
INNER JOIN tblSwimmersTimes ON tblDistance.DistanceID =
tblSwimmersTimes.DistanceID) ON tblLevel.LevelID =
tblSwimmersTimes.LevelID) ON tblStroke.StrokeID =
tblSwimmersTimes.StrokeID) ON tblSwimmer.SwimmerID =
tblSwimmersTimes.SwimmerID) ON tblVenue.VenueID =
tblSwimmersTimes.VenueID
GROUP BY tblSwimmersTimes.SwimmerID,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
HAVING (((tblStroke.Stroke)="Freestyle") AND
((tblDistance.Distance)="50m"))
ORDER BY Min(tblSwimmersTimes.PB);


Thanks

Pete

 

[Andreas]

The query as per original post should give you a single record for each
swimmer and the fastest time for that swimmer. If you get more than one
record for a swimmer, you are doing something wrong.

Regards,
Andreas

 

[Andreas]

Please ignore my last post regarding this. I had not scrolled up to see
the new posts (I was wondering about the large gap).

Create a new query using tblSwimmersTimes as your datasource.
Bring in only the fields SwimmerID and PB.
Follow the original instructions.

Then open the original query (as per below), click on the Sigma button
to remove the Totals row and reset the Top Values option to All.

Then add the new query above as a data source.

Create a link between the fields from this query and the table
tblSwimmersTimes: SwimmerID to SwimmerID, MinOfPB to PB.

Additionally, may be look up help on Summary Totals queries and your
mistake will be immediately obvious.

Regards,
Andreas

SELECT DISTINCT TOP 10 tblSwimmersTimes.SwimmerID, Min
(tblSwimmersTimes.PB) AS MinOfPB,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN
(tblStroke INNER JOIN (tblLevel INNER JOIN (tblDistance
INNER JOIN tblSwimmersTimes ON tblDistance.DistanceID =
tblSwimmersTimes.DistanceID) ON tblLevel.LevelID =
tblSwimmersTimes.LevelID) ON tblStroke.StrokeID =
tblSwimmersTimes.StrokeID) ON tblSwimmer.SwimmerID =
tblSwimmersTimes.SwimmerID) ON tblVenue.VenueID =
tblSwimmersTimes.VenueID
GROUP BY tblSwimmersTimes.SwimmerID,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
HAVING (((tblStroke.Stroke)="Freestyle") AND
((tblDistance.Distance)="50m"))
ORDER BY Min(tblSwimmersTimes.PB);


Thanks

Pete

-----Original Message-----
Open the query in design view, click on the "Query view"

icon on toolbar,

select SQL view, and copy the entire statement that

you'll see in that

window.

Paste it into a reply to this message so that we can see

what the query is

that you're trying to use.

--

Ken Snell
<MS ACCESS MVP>

message

words "GoupBy"

show

event,

above



.

 

[Ken Snell [MVP]]

It appears that you have a number of variables here that you're trying to
use: swimmer, venue, level, stroke, distance, etc.

Rather than try to sort through this with numerous assumptions, tell us
exactly what you want the query to show... the fastest ten swimmers
regardless of distance and stroke and venue and level? or the fastest 10
swimmers for each venue and for each level and for each stroke and for each
distance...etc.


--

Ken Snell
<MS ACCESS MVP>

SELECT DISTINCT TOP 10 tblSwimmersTimes.SwimmerID, Min
(tblSwimmersTimes.PB) AS MinOfPB,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN
(tblStroke INNER JOIN (tblLevel INNER JOIN (tblDistance
INNER JOIN tblSwimmersTimes ON tblDistance.DistanceID =
tblSwimmersTimes.DistanceID) ON tblLevel.LevelID =
tblSwimmersTimes.LevelID) ON tblStroke.StrokeID =
tblSwimmersTimes.StrokeID) ON tblSwimmer.SwimmerID =
tblSwimmersTimes.SwimmerID) ON tblVenue.VenueID =
tblSwimmersTimes.VenueID
GROUP BY tblSwimmersTimes.SwimmerID,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
HAVING (((tblStroke.Stroke)="Freestyle") AND
((tblDistance.Distance)="50m"))
ORDER BY Min(tblSwimmersTimes.PB);


Thanks

Pete

-----Original Message-----
Open the query in design view, click on the "Query view" icon on toolbar,
select SQL view, and copy the entire statement that you'll see in that
window.

Paste it into a reply to this message so that we can see what the query is
that you're trying to use.

--

Ken Snell
<MS ACCESS MVP>




.

 

[Pete]

All I want is to show the fastest 10 swimmers for any
given stroke & distance. I intend to use a parameter to
get the stroke.

E.G I would like the query to give me the top 10 50m
Freestyle swimmers in the example above.

Peter

-----Original Message-----
It appears that you have a number of variables here that you're trying to
use: swimmer, venue, level, stroke, distance, etc.

Rather than try to sort through this with numerous assumptions, tell us
exactly what you want the query to show... the fastest ten swimmers
regardless of distance and stroke and venue and level? or the fastest 10
swimmers for each venue and for each level and for each stroke and for each
distance...etc.


--

Ken Snell
<MS ACCESS MVP>

SELECT DISTINCT TOP 10 tblSwimmersTimes.SwimmerID, Min
(tblSwimmersTimes.PB) AS MinOfPB,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN
(tblStroke INNER JOIN (tblLevel INNER JOIN (tblDistance
INNER JOIN tblSwimmersTimes ON tblDistance.DistanceID =
tblSwimmersTimes.DistanceID) ON tblLevel.LevelID =
tblSwimmersTimes.LevelID) ON tblStroke.StrokeID =
tblSwimmersTimes.StrokeID) ON tblSwimmer.SwimmerID =
tblSwimmersTimes.SwimmerID) ON tblVenue.VenueID =
tblSwimmersTimes.VenueID
GROUP BY tblSwimmersTimes.SwimmerID,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
HAVING (((tblStroke.Stroke)="Freestyle") AND
((tblDistance.Distance)="50m"))
ORDER BY Min(tblSwimmersTimes.PB);


Thanks

Pete

-----Original Message-----
Open the query in design view, click on the "Query

view"
icon on toolbar,

select SQL view, and copy the entire statement that you'll see in that
window.

Paste it into a reply to this message so that we can

see
what the query is

that you're trying to use.

--

Ken Snell
<MS ACCESS MVP>

Hi Doug,

I'm still getting a swimmer with 2 records. I must be
doing something wrong.

I've clicked the AutoSum button to get the GroupBy up,
selected Min for the Time and sorted Ascending

How do I GroupBy swimmer, it displays the words "GoupBy"
under the SwimmerID filed, but the 1st two times
displayed are from the same swimmer.

I'm a bit of a newbie, can you point me in the right
direction

Pete

-----Original Message-----
The solution Andreas posted will do that: note the
mention of grouping by
swimmer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
how do I only display the quickest time if a swimmer
has
two or more records in the query.

e.g

Jim Smith 1.23.45
Jim Smith 1.24.56

I would only want to see the quickest time

Hope you can help

Pete

-----Original Message-----
Use a summary totals query to find the Min value for
each swimmer (Sigma
button on toolbar, Group By swimmer, Min for time).
Use the Top Values option (on toolbar) to limit the
number of records shown.
Make sure you sort by times (ascending).

Regards,
Andreas


Pete wrote:
I have a query which I would like to display the 10
quickest swimmers. How can I limit the query to show
only
10 records.

Secondly, 1 swimmer may have many times for an event,
but
I only want the quickest time to display in the above
question.

Pete


.



.



.

.

 

[Pete]

Andreas,

I'm struggling to follow this, can we start again.

1. Create a query using tblSwimmersTimes
include SwimmerID & PB
GroupBy SwimmerID
GroupBy Min for PB & sort Ascending

I've got that far, and yes it gives me only one swimmer
as you say. I need to be able to swap between strokes &
distances, this only gives me the fastest 10 times
regardless of stroke & distance.

To choose the stroke, distance & sex of swimmer ( I may
want to display top 10 female Breaststrokers, or top 10
Male Backstrokers. I will be using a form to provide the
criteria for this query.

2. I'm lost here, when you say link between fields, can
we go slower.

Please keep helping me, I think you know what I mean now,
but I am very inexperienced with Access

Thanks

Pete

-----Original Message-----
Please ignore my last post regarding this. I had not scrolled up to see
the new posts (I was wondering about the large gap).

Create a new query using tblSwimmersTimes as your datasource.
Bring in only the fields SwimmerID and PB.
Follow the original instructions.

Then open the original query (as per below), click on the Sigma button
to remove the Totals row and reset the Top Values option to All.

Then add the new query above as a data source.

Create a link between the fields from this query and the table
tblSwimmersTimes: SwimmerID to SwimmerID, MinOfPB to PB.

Additionally, may be look up help on Summary Totals queries and your
mistake will be immediately obvious.

Regards,
Andreas

SELECT DISTINCT TOP 10 tblSwimmersTimes.SwimmerID, Min
(tblSwimmersTimes.PB) AS MinOfPB,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN
(tblStroke INNER JOIN (tblLevel INNER JOIN (tblDistance
INNER JOIN tblSwimmersTimes ON tblDistance.DistanceID =
tblSwimmersTimes.DistanceID) ON tblLevel.LevelID =
tblSwimmersTimes.LevelID) ON tblStroke.StrokeID =
tblSwimmersTimes.StrokeID) ON tblSwimmer.SwimmerID =
tblSwimmersTimes.SwimmerID) ON tblVenue.VenueID =
tblSwimmersTimes.VenueID
GROUP BY tblSwimmersTimes.SwimmerID,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
HAVING (((tblStroke.Stroke)="Freestyle") AND
((tblDistance.Distance)="50m"))
ORDER BY Min(tblSwimmersTimes.PB);


Thanks

Pete

-----Original Message-----
Open the query in design view, click on the "Query

view"

icon on toolbar,

select SQL view, and copy the entire statement that

you'll see in that

window.

Paste it into a reply to this message so that we can

see

what the query is

that you're trying to use.

--

Ken Snell
<MS ACCESS MVP>

message


Hi Doug,

I'm still getting a swimmer with 2 records. I must be
doing something wrong.

I've clicked the AutoSum button to get the GroupBy up,
selected Min for the Time and sorted Ascending

How do I GroupBy swimmer, it displays the

words "GoupBy"

under the SwimmerID filed, but the 1st two times
displayed are from the same swimmer.

I'm a bit of a newbie, can you point me in the right
direction

Pete


-----Original Message-----
The solution Andreas posted will do that: note the

mention of grouping by

swimmer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)




message


how do I only display the quickest time if a swimmer

has

two or more records in the query.

e.g

Jim Smith 1.23.45
Jim Smith 1.24.56

I would only want to see the quickest time

Hope you can help

Pete


-----Original Message-----
Use a summary totals query to find the Min value for

each swimmer (Sigma

button on toolbar, Group By swimmer, Min for time).
Use the Top Values option (on toolbar) to limit the

number of records shown.

Make sure you sort by times (ascending).

Regards,
Andreas


Pete wrote:

I have a query which I would like to display the 10
quickest swimmers. How can I limit the query to
show

only

10 records.

Secondly, 1 swimmer may have many times for an
event,

but

I only want the quickest time to display in the
above

question.

Pete


.



.



.

.

 

[Ken Snell [MVP]]

Try this (not tested):

SELECT tblSwimmersTimes.SwimmerID,
tblSwimmersTimes.PB AS MinOfPB,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN
(tblStroke INNER JOIN (tblLevel INNER JOIN (tblDistance
INNER JOIN tblSwimmersTimes ON tblDistance.DistanceID =
tblSwimmersTimes.DistanceID) ON tblLevel.LevelID =
tblSwimmersTimes.LevelID) ON tblStroke.StrokeID =
tblSwimmersTimes.StrokeID) ON tblSwimmer.SwimmerID =
tblSwimmersTimes.SwimmerID) ON tblVenue.VenueID =
tblSwimmersTimes.VenueID
WHERE tblSwimmersTimes.SwimmerID IN
(SELECT TOP 10 T.SwimmerID FROM SwimmersTimes AS T
INNER JOIN tblStroke AS TS ON T.StrokeID = TS.StrokeID
WHERE T.DistanceID=tblSwimmersTimes.DistanceID AND
TS.StrokeID = tblStroke.StrokeID
ORDER BY T.PB DESC)
AND tblStroke.Stroke=[Enter stroke type:] AND
tblDistance.Distance=[Enter distance type:]
ORDER BY tblSwimmersTimes.PB;

--

Ken Snell
<MS ACCESS MVP>

All I want is to show the fastest 10 swimmers for any
given stroke & distance. I intend to use a parameter to
get the stroke.

E.G I would like the query to give me the top 10 50m
Freestyle swimmers in the example above.

Peter

-----Original Message-----
It appears that you have a number of variables here that you're trying to
use: swimmer, venue, level, stroke, distance, etc.

Rather than try to sort through this with numerous assumptions, tell us
exactly what you want the query to show... the fastest ten swimmers
regardless of distance and stroke and venue and level? or the fastest 10
swimmers for each venue and for each level and for each stroke and for each
distance...etc.


--

Ken Snell
<MS ACCESS MVP>

SELECT DISTINCT TOP 10 tblSwimmersTimes.SwimmerID, Min
(tblSwimmersTimes.PB) AS MinOfPB,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN
(tblStroke INNER JOIN (tblLevel INNER JOIN (tblDistance
INNER JOIN tblSwimmersTimes ON tblDistance.DistanceID =
tblSwimmersTimes.DistanceID) ON tblLevel.LevelID =
tblSwimmersTimes.LevelID) ON tblStroke.StrokeID =
tblSwimmersTimes.StrokeID) ON tblSwimmer.SwimmerID =
tblSwimmersTimes.SwimmerID) ON tblVenue.VenueID =
tblSwimmersTimes.VenueID
GROUP BY tblSwimmersTimes.SwimmerID,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
HAVING (((tblStroke.Stroke)="Freestyle") AND
((tblDistance.Distance)="50m"))
ORDER BY Min(tblSwimmersTimes.PB);


Thanks

Pete
-----Original Message-----
Open the query in design view, click on the "Query view"
icon on toolbar,
select SQL view, and copy the entire statement that
you'll see in that
window.

Paste it into a reply to this message so that we can see
what the query is
that you're trying to use.

--

Ken Snell
<MS ACCESS MVP>

message
Hi Doug,

I'm still getting a swimmer with 2 records. I must be
doing something wrong.

I've clicked the AutoSum button to get the GroupBy up,
selected Min for the Time and sorted Ascending

How do I GroupBy swimmer, it displays the
words "GoupBy"
under the SwimmerID filed, but the 1st two times
displayed are from the same swimmer.

I'm a bit of a newbie, can you point me in the right
direction

Pete

-----Original Message-----
The solution Andreas posted will do that: note the
mention of grouping by
swimmer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
how do I only display the quickest time if a swimmer
has
two or more records in the query.

e.g

Jim Smith 1.23.45
Jim Smith 1.24.56

I would only want to see the quickest time

Hope you can help

Pete

-----Original Message-----
Use a summary totals query to find the Min value for
each swimmer (Sigma
button on toolbar, Group By swimmer, Min for time).
Use the Top Values option (on toolbar) to limit the
number of records shown.
Make sure you sort by times (ascending).

Regards,
Andreas


Pete wrote:
I have a query which I would like to display the 10
quickest swimmers. How can I limit the query to
show
only
10 records.

Secondly, 1 swimmer may have many times for an
event,
but
I only want the quickest time to display in the
above
question.

Pete


.



.



.

.

 

[Pete]

SELECT TOP 10 tblSwimmersTimes.SwimmerID, Min
(tblSwimmersTimes.PB) AS MinOfPB, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.[D-O-
B], tblStroke.Stroke, tblVenue.Venue,
tblDistance.Distance, tblSwimmersTimes.LevelID,
tblLevel.Level, AgeOnDay([D-O-B],Forms!frmTop10!MyDate)
AS AOD
FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN
(tblStroke INNER JOIN (tblLevel INNER JOIN (tblDistance
INNER JOIN tblSwimmersTimes ON
tblDistance.DistanceID=tblSwimmersTimes.DistanceID) ON
tblLevel.LevelID=tblSwimmersTimes.LevelID) ON
tblStroke.StrokeID=tblSwimmersTimes.StrokeID) ON
tblSwimmer.SwimmerID=tblSwimmersTimes.SwimmerID) ON
tblVenue.VenueID=tblSwimmersTimes.VenueID
WHERE (((tblSwimmersTimes.DistanceID)=Forms!frmTop10!
Distance) And ((tblSwimmersTimes.StrokeID)=Forms!frmTop10!
Stroke))
GROUP BY tblSwimmersTimes.SwimmerID,
tblSwimmer.FirstName, tblSwimmer.Initial,
tblSwimmer.Surname, tblSwimmer.[D-O-B], tblStroke.Stroke,
tblVenue.Venue, tblDistance.Distance,
tblSwimmersTimes.LevelID, tblLevel.Level, AgeOnDay([D-O-
B],Forms!frmTop10!MyDate), tblSwimmer.Sex
HAVING (((tblSwimmer.Sex)=Forms!frmTop10!Sex))
ORDER BY Min(tblSwimmersTimes.PB);

This gives me the top 10, but includes swimmers more than
once if the have two times in teh top 10. I tried your
version, but it wasn't giving me the 10 fastest. Please
see if you can help, this is depressing me, I thought
this would be easy.

Thanks

Pete

-----Original Message-----
Try this (not tested):

SELECT tblSwimmersTimes.SwimmerID,
tblSwimmersTimes.PB AS MinOfPB,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN
(tblStroke INNER JOIN (tblLevel INNER JOIN (tblDistance
INNER JOIN tblSwimmersTimes ON tblDistance.DistanceID =
tblSwimmersTimes.DistanceID) ON tblLevel.LevelID =
tblSwimmersTimes.LevelID) ON tblStroke.StrokeID =
tblSwimmersTimes.StrokeID) ON tblSwimmer.SwimmerID =
tblSwimmersTimes.SwimmerID) ON tblVenue.VenueID =
tblSwimmersTimes.VenueID
WHERE tblSwimmersTimes.SwimmerID IN
(SELECT TOP 10 T.SwimmerID FROM SwimmersTimes AS T
INNER JOIN tblStroke AS TS ON T.StrokeID = TS.StrokeID
WHERE T.DistanceID=tblSwimmersTimes.DistanceID AND
TS.StrokeID = tblStroke.StrokeID
ORDER BY T.PB DESC)
AND tblStroke.Stroke=[Enter stroke type:] AND
tblDistance.Distance=[Enter distance type:]
ORDER BY tblSwimmersTimes.PB;

--

Ken Snell
<MS ACCESS MVP>

All I want is to show the fastest 10 swimmers for any
given stroke & distance. I intend to use a parameter to
get the stroke.

E.G I would like the query to give me the top 10 50m
Freestyle swimmers in the example above.

Peter

-----Original Message-----
It appears that you have a number of variables here

that
you're trying to

use: swimmer, venue, level, stroke, distance, etc.

Rather than try to sort through this with numerous assumptions, tell us
exactly what you want the query to show... the fastest ten swimmers
regardless of distance and stroke and venue and level? or the fastest 10
swimmers for each venue and for each level and for each stroke and for each
distance...etc.


--

Ken Snell
<MS ACCESS MVP>


SELECT DISTINCT TOP 10 tblSwimmersTimes.SwimmerID, Min
(tblSwimmersTimes.PB) AS MinOfPB,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN
(tblStroke INNER JOIN (tblLevel INNER JOIN (tblDistance
INNER JOIN tblSwimmersTimes ON tblDistance.DistanceID =
tblSwimmersTimes.DistanceID) ON tblLevel.LevelID =
tblSwimmersTimes.LevelID) ON tblStroke.StrokeID =
tblSwimmersTimes.StrokeID) ON tblSwimmer.SwimmerID =
tblSwimmersTimes.SwimmerID) ON tblVenue.VenueID =
tblSwimmersTimes.VenueID
GROUP BY tblSwimmersTimes.SwimmerID,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID, tblSwimmersTimes.StrokeID,
tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname, tblSwimmer.Sex,
tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
HAVING (((tblStroke.Stroke)="Freestyle") AND
((tblDistance.Distance)="50m"))
ORDER BY Min(tblSwimmersTimes.PB);


Thanks

Pete
-----Original Message-----
Open the query in design view, click on the "Query view"
icon on toolbar,
select SQL view, and copy the entire statement that
you'll see in that
window.

Paste it into a reply to this message so that we can see
what the query is
that you're trying to use.

--

Ken Snell
<MS ACCESS MVP>

message
Hi Doug,

I'm still getting a swimmer with 2 records. I must be
doing something wrong.

I've clicked the AutoSum button to get the GroupBy up,
selected Min for the Time and sorted Ascending

How do I GroupBy swimmer, it displays the
words "GoupBy"
under the SwimmerID filed, but the 1st two times
displayed are from the same swimmer.

I'm a bit of a newbie, can you point me in the right
direction

Pete

-----Original Message-----
The solution Andreas posted will do that: note the
mention of grouping by
swimmer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
how do I only display the quickest time if a swimmer
has
two or more records in the query.

e.g

Jim Smith 1.23.45
Jim Smith 1.24.56

I would only want to see the quickest time

Hope you can help

Pete

-----Original Message-----
Use a summary totals query to find the Min value for
each swimmer (Sigma
button on toolbar, Group By swimmer, Min for time).
Use the Top Values option (on toolbar) to limit the
number of records shown.
Make sure you sort by times (ascending).

Regards,
Andreas


Pete wrote:
I have a query which I would like to display

the
10

quickest swimmers. How can I limit the query to
show
only
10 records.

Secondly, 1 swimmer may have many times for an
event,
but
I only want the quickest time to display in the
above
question.

Pete


.



.



.



.

.

 

[Andreas]

qryAnalysis01ExpandGroupFilterData:
- Add all tables providing data you want in the final result (and any
necessary linking tables) and bring down the appropriate fields. You
MUST have the fields "SwimmerID" and "PB". You can NOT have any other ID
fields except for "SwimmerID".
- Now, in a worst case scenario, you could end up with the same swimmer
having the same time twice for the same race. To resolve this, turn this
query into a "Summary Totals" query (Sigma button on toolbar) and ensure
you have the option "Group By" in the "Totals" row of every column.
- You could also have the same swimmer have an identical time in
different races (breast, back, butterfly, ...), so you will enter the
appropriate criteria to only return those records meeting the
appropriate criteria. If this requires additional fields which you don't
want in the final result, change the "Totals" row to "Where".

qryAnalysis02Top10:
- Add the table "tblSwimmersTimes". Bring down the fields "SwimmerID"
and "PB".
- Turn this query into a "Summary Totals" query (Sigma button on
toolbar) and ensure you have the option "Group By" in the "Totals" row
for the field "SwimmerID" and the option "Min" in the "Totals" row
for the field "PB".
- Sort the data by the field "PB", ascending for the fastest times.
- Change the Top Values option (on toolbar) to limit the number of
records shown to 10.
- To get only the fastest times for the option chosen, bring in the
appropriate tables, bring down the appropriate filter fields, change the
"Totals" row to "Where" and enter your criteria.

qryAnalysis:
- Add "qryAnalysis01ExpandGroupFilterData" and "qryAnalysis02Top10" as
your data sources.
- There should be no link between these. We can't have this! In the top
part of your query design view, drag the field "SwimmerID" to the other
field "SwimmerID". Also, drag the field "PB" to the field "MinOfPB".
- Now bring in all the fields you want to see in the final result.

There is only one thing left to go wrong. If, for example, you don't use
stroke as criteria and a swimmer gets the same time for different
strokes, you will get both records returned. The only way to get around
this is too complicated at this point, so we will live with it for now

Regards,
Andreas

Andreas,

I'm struggling to follow this, can we start again.

1. Create a query using tblSwimmersTimes
include SwimmerID & PB
GroupBy SwimmerID
GroupBy Min for PB & sort Ascending

I've got that far, and yes it gives me only one swimmer
as you say. I need to be able to swap between strokes &
distances, this only gives me the fastest 10 times
regardless of stroke & distance.

To choose the stroke, distance & sex of swimmer ( I may
want to display top 10 female Breaststrokers, or top 10
Male Backstrokers. I will be using a form to provide the
criteria for this query.

2. I'm lost here, when you say link between fields, can
we go slower.

Please keep helping me, I think you know what I mean now,
but I am very inexperienced with Access

Thanks

Pete

-----Original Message-----
Please ignore my last post regarding this. I had not

scrolled up to see

the new posts (I was wondering about the large gap).

Create a new query using tblSwimmersTimes as your
datasource.

Bring in only the fields SwimmerID and PB.
Follow the original instructions.

Then open the original query (as per below), click on

the Sigma button

to remove the Totals row and reset the Top Values option

to All.

Then add the new query above as a data source.

Create a link between the fields from this query and the
table

tblSwimmersTimes: SwimmerID to SwimmerID, MinOfPB to PB.

Additionally, may be look up help on Summary Totals

queries and your

mistake will be immediately obvious.

Regards,
Andreas

SELECT DISTINCT TOP 10 tblSwimmersTimes.SwimmerID, Min
(tblSwimmersTimes.PB) AS MinOfPB,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID,

tblSwimmersTimes.StrokeID,

tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname,

tblSwimmer.Sex,

tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN
(tblStroke INNER JOIN (tblLevel INNER JOIN

(tblDistance

INNER JOIN tblSwimmersTimes ON tblDistance.DistanceID

=

tblSwimmersTimes.DistanceID) ON tblLevel.LevelID =
tblSwimmersTimes.LevelID) ON tblStroke.StrokeID =
tblSwimmersTimes.StrokeID) ON tblSwimmer.SwimmerID =
tblSwimmersTimes.SwimmerID) ON tblVenue.VenueID =
tblSwimmersTimes.VenueID
GROUP BY tblSwimmersTimes.SwimmerID,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID,

tblSwimmersTimes.StrokeID,

tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname,

tblSwimmer.Sex,

tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
HAVING (((tblStroke.Stroke)="Freestyle") AND
((tblDistance.Distance)="50m"))
ORDER BY Min(tblSwimmersTimes.PB);


Thanks

Pete


-----Original Message-----
Open the query in design view, click on the "Query

view"

icon on toolbar,


select SQL view, and copy the entire statement that

you'll see in that


window.

Paste it into a reply to this message so that we can

see

what the query is


that you're trying to use.

--

Ken Snell
<MS ACCESS MVP>


message




Hi Doug,

I'm still getting a swimmer with 2 records. I must be
doing something wrong.

I've clicked the AutoSum button to get the GroupBy up,
selected Min for the Time and sorted Ascending

How do I GroupBy swimmer, it displays the

words "GoupBy"


under the SwimmerID filed, but the 1st two times
displayed are from the same swimmer.

I'm a bit of a newbie, can you point me in the right
direction

Pete



-----Original Message-----
The solution Andreas posted will do that: note the

mention of grouping by


swimmer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)




message




how do I only display the quickest time if a swimmer

has


two or more records in the query.

e.g

Jim Smith 1.23.45
Jim Smith 1.24.56

I would only want to see the quickest time

Hope you can help

Pete



-----Original Message-----
Use a summary totals query to find the Min value

for

each swimmer (Sigma


button on toolbar, Group By swimmer, Min for time).
Use the Top Values option (on toolbar) to limit the

number of records shown.


Make sure you sort by times (ascending).

Regards,
Andreas


Pete wrote:


I have a query which I would like to display the

10

quickest swimmers. How can I limit the query to

show


only


10 records.

Secondly, 1 swimmer may have many times for an

event,


but


I only want the quickest time to display in the

above


question.

Pete


.



.



.

.

 

[Pete]

Phew!!

Thanks Andreas, I now appear to have what I require. I
don't really understand what you have done, but it does
give me the Top 10 swimmers for the selected Distance &
Stroke.

Regards

Pete

-----Original Message-----
qryAnalysis01ExpandGroupFilterData:
- Add all tables providing data you want in the final result (and any
necessary linking tables) and bring down the appropriate fields. You
MUST have the fields "SwimmerID" and "PB". You can NOT have any other ID
fields except for "SwimmerID".
- Now, in a worst case scenario, you could end up with the same swimmer
having the same time twice for the same race. To resolve this, turn this
query into a "Summary Totals" query (Sigma button on toolbar) and ensure
you have the option "Group By" in the "Totals" row of every column.
- You could also have the same swimmer have an identical time in
different races (breast, back, butterfly, ...), so you will enter the
appropriate criteria to only return those records meeting the
appropriate criteria. If this requires additional fields which you don't
want in the final result, change the "Totals" row to "Where".

qryAnalysis02Top10:
- Add the table "tblSwimmersTimes". Bring down the fields "SwimmerID"
and "PB".
- Turn this query into a "Summary Totals" query (Sigma button on
toolbar) and ensure you have the option "Group By" in the "Totals" row
for the field "SwimmerID" and the option "Min" in the "Totals" row
for the field "PB".
- Sort the data by the field "PB", ascending for the fastest times.
- Change the Top Values option (on toolbar) to limit the number of
records shown to 10.
- To get only the fastest times for the option chosen, bring in the
appropriate tables, bring down the appropriate filter fields, change the
"Totals" row to "Where" and enter your criteria.

qryAnalysis:
- Add "qryAnalysis01ExpandGroupFilterData" and "qryAnalysis02Top10" as
your data sources.
- There should be no link between these. We can't have this! In the top
part of your query design view, drag the field "SwimmerID" to the other
field "SwimmerID". Also, drag the field "PB" to the field "MinOfPB".
- Now bring in all the fields you want to see in the final result.

There is only one thing left to go wrong. If, for example, you don't use
stroke as criteria and a swimmer gets the same time for different
strokes, you will get both records returned. The only way to get around
this is too complicated at this point, so we will live with it for now

Regards,
Andreas

Andreas,

I'm struggling to follow this, can we start again.

1. Create a query using tblSwimmersTimes
include SwimmerID & PB
GroupBy SwimmerID
GroupBy Min for PB & sort Ascending

I've got that far, and yes it gives me only one swimmer
as you say. I need to be able to swap between strokes &
distances, this only gives me the fastest 10 times
regardless of stroke & distance.

To choose the stroke, distance & sex of swimmer ( I may
want to display top 10 female Breaststrokers, or top 10
Male Backstrokers. I will be using a form to provide the
criteria for this query.

2. I'm lost here, when you say link between fields, can
we go slower.

Please keep helping me, I think you know what I mean now,
but I am very inexperienced with Access

Thanks

Pete

-----Original Message-----
Please ignore my last post regarding this. I had not

scrolled up to see

the new posts (I was wondering about the large gap).

Create a new query using tblSwimmersTimes as your
datasource.

Bring in only the fields SwimmerID and PB.
Follow the original instructions.

Then open the original query (as per below), click on

the Sigma button

to remove the Totals row and reset the Top Values

option

to All.

Then add the new query above as a data source.

Create a link between the fields from this query and

the

table

tblSwimmersTimes: SwimmerID to SwimmerID, MinOfPB to PB.

Additionally, may be look up help on Summary Totals

queries and your

mistake will be immediately obvious.

Regards,
Andreas

(e-mail address removed) wrote:

SELECT DISTINCT TOP 10 tblSwimmersTimes.SwimmerID, Min
(tblSwimmersTimes.PB) AS MinOfPB,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID,
tblSwimmersTimes.StrokeID,

tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname,
tblSwimmer.Sex,

tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN
(tblStroke INNER JOIN (tblLevel INNER JOIN
(tblDistance

INNER JOIN tblSwimmersTimes ON tblDistance.DistanceID
=

tblSwimmersTimes.DistanceID) ON tblLevel.LevelID =
tblSwimmersTimes.LevelID) ON tblStroke.StrokeID =
tblSwimmersTimes.StrokeID) ON tblSwimmer.SwimmerID =
tblSwimmersTimes.SwimmerID) ON tblVenue.VenueID =
tblSwimmersTimes.VenueID
GROUP BY tblSwimmersTimes.SwimmerID,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID,
tblSwimmersTimes.StrokeID,

tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname,
tblSwimmer.Sex,

tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
HAVING (((tblStroke.Stroke)="Freestyle") AND
((tblDistance.Distance)="50m"))
ORDER BY Min(tblSwimmersTimes.PB);


Thanks

Pete


-----Original Message-----
Open the query in design view, click on the "Query
view"

icon on toolbar,


select SQL view, and copy the entire statement that

you'll see in that


window.

Paste it into a reply to this message so that we can
see

what the query is


that you're trying to use.

--

Ken Snell
<MS ACCESS MVP>


message




Hi Doug,

I'm still getting a swimmer with 2 records. I must be
doing something wrong.

I've clicked the AutoSum button to get the GroupBy up,
selected Min for the Time and sorted Ascending

How do I GroupBy swimmer, it displays the

words "GoupBy"


under the SwimmerID filed, but the 1st two times
displayed are from the same swimmer.

I'm a bit of a newbie, can you point me in the right
direction

Pete



-----Original Message-----
The solution Andreas posted will do that: note the

mention of grouping by


swimmer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)




message




how do I only display the quickest time if a swimmer

has


two or more records in the query.

e.g

Jim Smith 1.23.45
Jim Smith 1.24.56

I would only want to see the quickest time

Hope you can help

Pete



-----Original Message-----
Use a summary totals query to find the Min value
for

each swimmer (Sigma


button on toolbar, Group By swimmer, Min for time).
Use the Top Values option (on toolbar) to limit the

number of records shown.


Make sure you sort by times (ascending).

Regards,
Andreas


Pete wrote:


I have a query which I would like to display

the

10

quickest swimmers. How can I limit the query to

show


only


10 records.

Secondly, 1 swimmer may have many times for an

event,


but


I only want the quickest time to display in the

above


question.

Pete


.



.



.


.

.

 

[Andreas]

Phew indeed

Looks like my struggle with the keyboard has found it's just reward (I
am a 2 finger warrior)

Apart from saying "You are welcome", I'd also like to ask you to work
your way through this till you "get it" - otherwise you missed the whole
point! This is why I put a number of explanations with my instructions.
This is why I broke it into 3 parts. This is how you learn. This is how
you don't break the rule: "Never copy something unless you understand
how it works". I am rambling now ... sorry ...

Regards,
Andreas

Phew!!

Thanks Andreas, I now appear to have what I require. I
don't really understand what you have done, but it does
give me the Top 10 swimmers for the selected Distance &
Stroke.

Regards

Pete

-----Original Message-----
qryAnalysis01ExpandGroupFilterData:
- Add all tables providing data you want in the final

result (and any

necessary linking tables) and bring down the appropriate

fields. You

MUST have the fields "SwimmerID" and "PB". You can NOT

have any other ID

fields except for "SwimmerID".
- Now, in a worst case scenario, you could end up with

the same swimmer

having the same time twice for the same race. To resolve

this, turn this

query into a "Summary Totals" query (Sigma button on

toolbar) and ensure

you have the option "Group By" in the "Totals" row of

every column.

- You could also have the same swimmer have an identical

time in

different races (breast, back, butterfly, ...), so you

will enter the

appropriate criteria to only return those records

meeting the

appropriate criteria. If this requires additional fields

which you don't

want in the final result, change the "Totals" row

to "Where".

qryAnalysis02Top10:
- Add the table "tblSwimmersTimes". Bring down the

fields "SwimmerID"

and "PB".
- Turn this query into a "Summary Totals" query (Sigma

button on

toolbar) and ensure you have the option "Group By" in

the "Totals" row

for the field "SwimmerID" and the option "Min" in

the "Totals" row

for the field "PB".
- Sort the data by the field "PB", ascending for the

fastest times.

- Change the Top Values option (on toolbar) to limit the

number of

records shown to 10.
- To get only the fastest times for the option chosen,

bring in the

appropriate tables, bring down the appropriate filter

fields, change the

"Totals" row to "Where" and enter your criteria.

qryAnalysis:
- Add "qryAnalysis01ExpandGroupFilterData"

and "qryAnalysis02Top10" as

your data sources.
- There should be no link between these. We can't have

this! In the top

part of your query design view, drag the

field "SwimmerID" to the other

field "SwimmerID". Also, drag the field "PB" to the

field "MinOfPB".

- Now bring in all the fields you want to see in the

final result.

There is only one thing left to go wrong. If, for

example, you don't use

stroke as criteria and a swimmer gets the same time for
different

strokes, you will get both records returned. The only

way to get around

this is too complicated at this point, so we will live

with it for now

Regards,
Andreas

Andreas,

I'm struggling to follow this, can we start again.

1. Create a query using tblSwimmersTimes
include SwimmerID & PB
GroupBy SwimmerID
GroupBy Min for PB & sort Ascending

I've got that far, and yes it gives me only one

swimmer

as you say. I need to be able to swap between strokes

&

distances, this only gives me the fastest 10 times
regardless of stroke & distance.

To choose the stroke, distance & sex of swimmer ( I

may

want to display top 10 female Breaststrokers, or top

10

Male Backstrokers. I will be using a form to provide

the

criteria for this query.

2. I'm lost here, when you say link between fields,

can

we go slower.

Please keep helping me, I think you know what I mean

now,

but I am very inexperienced with Access

Thanks

Pete


-----Original Message-----
Please ignore my last post regarding this. I had not

scrolled up to see


the new posts (I was wondering about the large gap).

Create a new query using tblSwimmersTimes as your

datasource.


Bring in only the fields SwimmerID and PB.
Follow the original instructions.

Then open the original query (as per below), click on

the Sigma button


to remove the Totals row and reset the Top Values

option

to All.


Then add the new query above as a data source.

Create a link between the fields from this query and

the

table


tblSwimmersTimes: SwimmerID to SwimmerID, MinOfPB to

PB.

Additionally, may be look up help on Summary Totals

queries and your


mistake will be immediately obvious.

Regards,
Andreas

(e-mail address removed) wrote:


SELECT DISTINCT TOP 10 tblSwimmersTimes.SwimmerID, Min
(tblSwimmersTimes.PB) AS MinOfPB,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID,

tblSwimmersTimes.StrokeID,


tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname,

tblSwimmer.Sex,


tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue

FROM tblVenue INNER JOIN (tblSwimmer INNER JOIN

(tblStroke INNER JOIN (tblLevel INNER JOIN

(tblDistance


INNER JOIN tblSwimmersTimes ON tblDistance.DistanceID

=


tblSwimmersTimes.DistanceID) ON tblLevel.LevelID =
tblSwimmersTimes.LevelID) ON tblStroke.StrokeID =
tblSwimmersTimes.StrokeID) ON tblSwimmer.SwimmerID =
tblSwimmersTimes.SwimmerID) ON tblVenue.VenueID =
tblSwimmersTimes.VenueID
GROUP BY tblSwimmersTimes.SwimmerID,
tblSwimmersTimes.SwimmersTimesID,
tblSwimmersTimes.DateSet, tblSwimmersTimes.VenueID,
tblSwimmersTimes.DistanceID,

tblSwimmersTimes.StrokeID,


tblSwimmersTimes.LevelID, tblSwimmer.FirstName,
tblSwimmer.Initial, tblSwimmer.Surname,

tblSwimmer.Sex,


tblSwimmer.[D-O-B], tblStroke.Stroke, tblLevel.Level,
tblDistance.Distance, tblVenue.Venue
HAVING (((tblStroke.Stroke)="Freestyle") AND
((tblDistance.Distance)="50m"))
ORDER BY Min(tblSwimmersTimes.PB);


Thanks

Pete



-----Original Message-----
Open the query in design view, click on the "Query

view"


icon on toolbar,



select SQL view, and copy the entire statement that

you'll see in that



window.

Paste it into a reply to this message so that we can

see


what the query is



that you're trying to use.

--

Ken Snell
<MS ACCESS MVP>

"Pete" <[email protected]> wrote

in

message






Hi Doug,

I'm still getting a swimmer with 2 records. I must

be

doing something wrong.

I've clicked the AutoSum button to get the GroupBy

up,

selected Min for the Time and sorted Ascending

How do I GroupBy swimmer, it displays the

words "GoupBy"



under the SwimmerID filed, but the 1st two times
displayed are from the same swimmer.

I'm a bit of a newbie, can you point me in the right
direction

Pete




-----Original Message-----
The solution Andreas posted will do that: note the

mention of grouping by



swimmer.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Pete" <[email protected]> wrote

in

message






how do I only display the quickest time if a

swimmer

has



two or more records in the query.

e.g

Jim Smith 1.23.45
Jim Smith 1.24.56

I would only want to see the quickest time

Hope you can help

Pete




-----Original Message-----
Use a summary totals query to find the Min value

for


each swimmer (Sigma



button on toolbar, Group By swimmer, Min for

time).

Use the Top Values option (on toolbar) to limit

the

number of records shown.



Make sure you sort by times (ascending).

Regards,
Andreas


Pete wrote:



I have a query which I would like to display

the

10


quickest swimmers. How can I limit the query to

show



only



10 records.

Secondly, 1 swimmer may have many times for an

event,



but



I only want the quickest time to display in the

above



question.

Pete


.



.



.


.

.