Need help with a query to display top 3 of a group

[Ten Twenty via AccessMonster.com]

The query below return this data in it's rows:

Rank # Name Div Sex Score
1 8 Bird Twitty 52Kg Female 684.69
2 12 du Toit Carin 52Kg Female 608.77
3 13 Colbert Jackie 52Kg Female 607.62
4 11 Wade Jenna 52Kg Female 595.41
1 7 DeDee Jenna 67.5Kg Female 702.27
2 10 Diva Meisie 67.5Kg Female 550.46
1 5 Man Super 70Kg Male 655.09
1 9 Doe John 82.5Kg Male 480.3
1 3 Man Spider 90Kg Male 537.63
1 6 America Mr 100Kg Male 406.42
1 4 Hulk Incred 110Kg Male 645.8

SELECT (SELECT COUNT(*) + 1
FROM History H INNER JOIN
Lifter L ON L.uidLifter = H.uidLifterRef
WHERE L.uidClassRef = Lifter.uidClassRef AND
H.ScoreTotal > History.ScoreTotal) AS Rank,
Lifter.LifterNumber AS LifterNumber,
Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname,
Class.Description AS CDescription,
Activity.Description AS GDescription,
History.ScoreTotal AS ScoreTotal
FROM Class, Lifter, Team, History, Meet, Activity
WHERE (Meet.MeetDate = DATE ()) AND Team.TeamStatus = 0 AND
History.ScoreTotal > 0 AND
Class.uidClass = Lifter.uidClassRef AND
Lifter.uidTeamRef = Team.uidTeam AND
Lifter.uidLifter = History.uidLifterRef AND
Team.uidMeetRef = Meet.uidMeet AND
Lifter.Gender = Activity.Code
GROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname,
Lifter.Firstname, Class.Description, History.WeightScoreC,
Lifter.Gender, Activity.Description, Activity.Activity,
Lifter.LifterStatus, History.ScoreTotal
HAVING (Activity.Activity = 'GenderStatus') AND
(Lifter.LifterStatus = 0)
ORDER BY VAL(Class.Description) ASC, Lifter.Gender DESC,
History.ScoreTotal DESC

I would like to see the query only returns the top three of each group to help me determine the GOLD,SILVER and BRONCE position in each division.

 

[Jeff Boyce]

Have you checked into the "TOP n" expression in Access HELP, or searched re:
the "TOP" property?

--
Good luck

Jeff Boyce
<Access MVP>

The query below return this data in it's rows:

Rank # Name Div Sex Score
1 8 Bird Twitty 52Kg Female 684.69
2 12 du Toit Carin 52Kg Female 608.77
3 13 Colbert Jackie 52Kg Female 607.62
4 11 Wade Jenna 52Kg Female 595.41
1 7 DeDee Jenna 67.5Kg Female 702.27
2 10 Diva Meisie 67.5Kg Female 550.46
1 5 Man Super 70Kg Male 655.09
1 9 Doe John 82.5Kg Male 480.3
1 3 Man Spider 90Kg Male 537.63
1 6 America Mr 100Kg Male 406.42
1 4 Hulk Incred 110Kg Male 645.8

SELECT (SELECT COUNT(*) + 1
FROM History H INNER JOIN
Lifter L ON L.uidLifter = H.uidLifterRef
WHERE L.uidClassRef = Lifter.uidClassRef AND
H.ScoreTotal > History.ScoreTotal) AS Rank,
Lifter.LifterNumber AS LifterNumber,
Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname,
Class.Description AS CDescription,
Activity.Description AS GDescription,
History.ScoreTotal AS ScoreTotal
FROM Class, Lifter, Team, History, Meet, Activity
WHERE (Meet.MeetDate = DATE ()) AND Team.TeamStatus = 0 AND
History.ScoreTotal > 0 AND
Class.uidClass = Lifter.uidClassRef AND
Lifter.uidTeamRef = Team.uidTeam AND
Lifter.uidLifter = History.uidLifterRef AND
Team.uidMeetRef = Meet.uidMeet AND
Lifter.Gender = Activity.Code
GROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname,
Lifter.Firstname, Class.Description, History.WeightScoreC,
Lifter.Gender, Activity.Description, Activity.Activity,
Lifter.LifterStatus, History.ScoreTotal
HAVING (Activity.Activity = 'GenderStatus') AND
(Lifter.LifterStatus = 0)
ORDER BY VAL(Class.Description) ASC, Lifter.Gender DESC,
History.ScoreTotal DESC

I would like to see the query only returns the top three of each group to

help me determine the GOLD,SILVER and BRONCE position in each division.

 

[Ten Twenty via AccessMonster.com]

If I use the Top 3 statement it would only return the first 3 records of the first division while I would like to see the first 3 records of each division.

 

[Jeff Boyce]

Here's a post by Duane H. on the topic...

Build the report to sort and group by District and Profit. Place a text box
in the detail section:
Name: txtSequence
Control Source: = 1
Running Sum: Over Group
Then in code in the On Format event of the detail section
Cancel = Me.txtSequence > 5

Another method would be to use a subquery, something like (air sql):
SELECT *
FROM tblSales
WHERE ProductID IN (SELECT Top 5 ProductID FROM tblSales S WHERE
S.DistrictID = tblSales.DistrictID ORDER BY Profit DESC)
--
Duane Hookom
Microsoft Access MVP


Good luck

Jeff Boyce
<Access MVP>

If I use the Top 3 statement it would only return the first 3 records of

the first division while I would like to see the first 3 records of each
division.

 

[Ten Twenty via AccessMonster.com]

I would like to go for the subquery. I tried it above but just can get it go'in. If you are interested, I can send you the database if you would like to have a dab at it.

 

[Jeff Boyce]

My responses (and posts) in the 'groups are voluntary. I fit them in around
my day job, my outside consulting, my family, my sports, my "chores" and my
sleep. I really can't devote the time to work my way through your database,
although other readers here may be able to.

One purpose of the newsgroups, in my opinion, is to provide a public venue
for issues, that all readers may benefit. I'd suggest keeping this in the
newsgroup!

To continue the thread, please provide some idea of what you mean when you
say "just can['t] get it [to] go 'in'". Go in where? What happens when you
try?

--
Good luck

Jeff Boyce
<Access MVP>

I would like to go for the subquery. I tried it above but just can get it

go'in. If you are interested, I can send you the database if you would like
to have a dab at it.

 

[Ten Twenty via AccessMonster.com]

Hi there,
As you can see here is the changes as sugested by you implemented but it is clear to the result that I'm doing something wrong or that I could have misunderstood you.

I don't ask anyone to the work for me, I merely ask if someone can perhaps see what I'm doing wrong in this statement. I appreciate every effort by everyone who take a minute of their time to look or help, which surely includes you. Thanks!

My approach in solving the problem I have might be competely wrong and someone can point that out to me.

When I help people I always like to have as much information available to me to evaluate and work from, that's why the suggestion of the database. If you felt that in asking you to have a dab at it was uncalled for, SORRY.


SELECT (SELECT COUNT(*) + 1
FROM History H INNER JOIN
Lifter L ON L.uidLifter = H.uidLifterRef
WHERE L.uidClassRef = Lifter.uidClassRef AND
H.ScoreTotal > History.ScoreTotal) AS Rank,
Lifter.LifterNumber AS LifterNumber,
Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname,
Class.Description AS CDescription,
Activity.Description AS GDescription,
History.ScoreTotal AS ScoreTotal
FROM Class, Lifter, Team, History, Meet, Activity
WHERE (Meet.MeetDate = DATE ()) AND Team.TeamStatus = 0 AND
History.ScoreTotal > 0 AND
Class.uidClass = Lifter.uidClassRef AND
Lifter.uidTeamRef = Team.uidTeam AND
Lifter.uidLifter = History.uidLifterRef AND
Team.uidMeetRef = Meet.uidMeet AND
Lifter.Gender = Activity.Code AND (Lifter.uidLifter IN
(SELECT Top 3 Lt.uidLifter
FROM Lifter Lt
WHERE Lt.uidLifter = Lifter.uidLifter))
GROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname,
Lifter.Firstname, Class.Description, History.WeightScoreC,
Lifter.Gender, Activity.Description, Activity.Activity,
Lifter.LifterStatus, History.ScoreTotal
HAVING (Activity.Activity = 'GenderStatus') AND
(Lifter.LifterStatus = 0)
ORDER BY VAL(Class.Description) ASC, Lifter.Gender DESC,
History.ScoreTotal DESC

 

[Jeff Boyce]

What happens if you simplify back to the minimum? Your query includes a lot
of extra fields and this may be masking what is/isn't working. By the way,
what isn't working about your query -- what result do you get?

--
Good luck

Jeff Boyce
<Access MVP>

Hi there,
As you can see here is the changes as sugested by you implemented but it

is clear to the result that I'm doing something wrong or that I could have
misunderstood you.

I don't ask anyone to the work for me, I merely ask if someone can perhaps

see what I'm doing wrong in this statement. I appreciate every effort by
everyone who take a minute of their time to look or help, which surely
includes you. Thanks!

My approach in solving the problem I have might be competely wrong and

someone can point that out to me.

When I help people I always like to have as much information available to

me to evaluate and work from, that's why the suggestion of the database. If
you felt that in asking you to have a dab at it was uncalled for, SORRY.