By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,648 Members | 1,558 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,648 IT Pros & Developers. It's quick & easy.

Report needs to display only top 3 winners in each class. (for trophies)

P: 49
This question seems to be common. However all the answers don't seem to work exactly like I need it.

I have about 1300 records in a table called DATA with names, over 30 classes and scores from 0 to 100.

I need only the top 3 scores in each class to display in a report for the announcer to hand out trophies.
Aug 3 '10 #1

✓ answered by Daniel Yantis

Using the AS DUPE I was able to get it working with only 1 query. none if you put the SQL directly in Record Source. Also, by removing DISTINCT I can see ties and fix them prior to handing out trophies.
Here is the final SQL:

SELECT Dupe.Class, Dupe.Score, Dupe.Name
FROM Data AS Dupe
WHERE (((Dupe.Score)<>0
And (Dupe.Score)
In (SELECT TOP 3 Data.Score
FROM Data
WHERE (((Data.Class)=Dupe.[Class]))
ORDER BY Data.Score DESC)))
ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.Name;

Share this Question
Share on Google+
13 Replies


code green
Expert 100+
P: 1,726
Use TOP and GROUP BY
Aug 3 '10 #2

100+
P: 374
Create a query with the scores/names of the people involved. Then at the top next to the sum symbol there will be a combo box which says "ALL" change this to a different value.

Then change your view to sql and edit the string to say TOP 3 instead of TOP valueyouselectedfromcombo.

Give the query a test to see if it provides what you want and then base your report source on this query.

Sorry for rushed response about to go to a meeting if it doesnt make sense I can explain better later.
Aug 3 '10 #3

P: 49
I tried that way before posting my question. Top 3 only shows the top 3 total records. I need to get the top 3 from each class. The following almost works...

http://bytes.com/topic/access/answer...-query-winners

Query1
SELECT DISTINCT Data.Class, Data.Score
FROM Data
ORDER BY Data.Class, Data.Score DESC;

Query2
SELECT Data.Class, Data.Score
FROM Data
WHERE (((Data.Score) In (Select Top 3 Score From Query1 Where Class = [Class])))
ORDER BY Data.Class, Data.Score DESC;

The problem is that the results only show the top 3 from each class where all scores are >= the 3rd value in the first set of 3. That was weird but very close.

Class Score
**** 89
**** 68
**** 59
AMCS 68
FCS 68
FCS 68
FCS 59
FDM 68
FFM 59
FFS 59
ICCF 68
RC 59
SCHM 68
SCZF 59
SUM 68

I should have numerious records with top 3 like 10, 15, 35 - notice that in above example the lowest score = 59 (lowest from top 3 in class 1)
Aug 3 '10 #4

code green
Expert 100+
P: 1,726
GROUP BY class
Aug 3 '10 #5

P: 49
where do I put the GROUP BY class? Please explain a little more. I'm not an expert on SQL syntax.
Aug 3 '10 #6

dsatino
100+
P: 393
How familiar with VBA are you?
Aug 3 '10 #7

Expert 100+
P: 107
Group By would go between the Where and Order By clauses.
Aug 3 '10 #8

P: 49
SELECT Data.Class, Data.Score
FROM Data
WHERE (((Data.Score) In (Select Top 3 Score From Query1 Where Class = [Class])))
GROUP BY Data.Class
ORDER BY Data.Class, Data.Score DESC;

produces the following error:

You tried to execute a query that does not include the specified expression <name> as part of an aggregate function.

GROUP BY Data.Class, Data.Score actually works but does nothing more than leaving GROUP BY out.
Aug 3 '10 #9

P: 49
Not, But I can copy code easily enough. I used to program in Pascal fairly well about 15 years ago. ;)
I am familiar with how programming works with loops and ifs.
Aug 3 '10 #10

dsatino
100+
P: 393
Well then the way I would do it is to open a recordset of only distinct class names. Loop through this recordset. At each row capture the class name as string variable. Use this string variable to open a new recordset that use the variable in the WHERE clause of the TOP 3 query. Essentially your running 30 different SQL queries, but with only 1300 records it will still be very fast.

I've tried the pure SQL method and have experienced the same quirks you have and also found it to be slower.
Aug 3 '10 #11

Expert 100+
P: 107
I got it to work with SQL, using 3 queries. The trick was to get the Where Class = [Class] to properly identify which query you are referring to. Note that if for 1 class there are three class scores of 100 then these will count as just one entry out of the top 3.

Query1:
SELECT DISTINCT Data.Class, Data.Score
FROM Data;

Query1Copy:
SELECT DISTINCT Data.Class, Data.Score
FROM Data;

Query2:
SELECT DISTINCT Query1Copy.Class, Query1Copy.Score
FROM Query1Copy
WHERE (((Query1Copy.Score) In (SELECT DISTINCT TOP 3 Query1.Score
FROM Query1
WHERE (((Query1.Class)=Query1Copy.[Class]))
ORDER BY Query1.Score DESC)))
ORDER BY Query1Copy.Class, Query1Copy.Score DESC;
Aug 3 '10 #12

P: 49
That actually worked! thanx. I'm not going to pretend I understand why/how it works, but as long as it does work I'm happy.
Aug 3 '10 #13

P: 49
Using the AS DUPE I was able to get it working with only 1 query. none if you put the SQL directly in Record Source. Also, by removing DISTINCT I can see ties and fix them prior to handing out trophies.
Here is the final SQL:

SELECT Dupe.Class, Dupe.Score, Dupe.Name
FROM Data AS Dupe
WHERE (((Dupe.Score)<>0
And (Dupe.Score)
In (SELECT TOP 3 Data.Score
FROM Data
WHERE (((Data.Class)=Dupe.[Class]))
ORDER BY Data.Score DESC)))
ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.Name;
Aug 5 '10 #14

Post your reply

Sign in to post your reply or Sign up for a free account.