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

Query for winners

P: n/a
Hello all,

I'm building a database for a high school academic convention and the big
task for this DB is to sort out the contest winners.

Here's the tables (slightly simplified):

tblDelegate
-----------
DelID
LastName
FirstName
School
etc. . .

tblContest
----------
ContID
ContName
etc. . .

tblResult
---------
DelID_FK
ContID_FK
Result

My question is how do I query so that I get the top three places in each
contest, including ties. So if Bob, Sue, Adam, Cindy, and Joe each take
the test in Latin Grammar and the Scores come out 10, 10, 9, 8, and 7
respectively, then the results should be

1st: Bob and Sue
2nd: Adam
3rd: Cindy

with Joe not placing. Using the top predicate would return just the 10s
and the 9 so Bob or Sue would be returned as the 1st, 2nd would be the
other between the two and Adam would be third.

Does anyone any have idea of how to approach this?

Thanks,

-Carlos
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Carlos Nunes-Ueno wrote:
Hello all,

I'm building a database for a high school academic convention and the big
task for this DB is to sort out the contest winners.

Here's the tables (slightly simplified):

tblDelegate
-----------
DelID
LastName
FirstName
School
etc. . .

tblContest
----------
ContID
ContName
etc. . .

tblResult
---------
DelID_FK
ContID_FK
Result

My question is how do I query so that I get the top three places in each
contest, including ties. So if Bob, Sue, Adam, Cindy, and Joe each take
the test in Latin Grammar and the Scores come out 10, 10, 9, 8, and 7
respectively, then the results should be

1st: Bob and Sue
2nd: Adam
3rd: Cindy

with Joe not placing. Using the top predicate would return just the 10s
and the 9 so Bob or Sue would be returned as the 1st, 2nd would be the
other between the two and Adam would be third.

Does anyone any have idea of how to approach this?

Thanks,

-Carlos


Maybe a mulipass query.

Create and follow what I do here to understand the concept. Cut/paste
the following SQL statements into new queries. You can adjust to your
situation once you follow/understand what I did.

I created a table called Table1. It has 2 fields; GroupID (similar to
the contest) and ScoreNum. I had 2 groups to test on, Group1 and
Group2. I added 6 records for group 1 with the scores 5,5,4,3,2,1. I
added 6 record for group 2 with the scores of 5,4,4,3,2,1.

I then created 2 queries; query1 and query2. Here is the SQL for Query1
SELECT DISTINCT Table1.groupid, Table1.Scorenum
FROM Table1
ORDER BY Table1.groupid, Table1.Scorenum DESC;
This is similar to a totals record where I get 1 record for each score
for each group. Thus for 5,5,4,3,2,1 I end up with 5 records for group1
or 5,4,3,2,1.

I then created the next query. Here's the SQL
SELECT Table1.groupid, Table1.Scorenum
FROM Table1
WHERE (((Table1.Scorenum) In (Select Top 3 ScoreNum From Query1 Where
GroupID = [GroupID])))
ORDER BY Table1.groupid;
I now know what the top 3 scores are for each group.

Now it is simply a matter of making a 3rd query where you link Query2 to
the scores field in your table to be able to list the names of the
winners. Use this query to display your winners.



Nov 12 '05 #2

P: n/a
Carlos,

You need to first determine the top three scores and then determine who attained
those scores. So you need at least two queries. The top three scores have the
additional requirement that they must be unique so along with the top 3
predicate you need to set Unique Values to true. This will give you 10, 9, and
8. Then you need another query to determine who achieved those scores and rank
order the names returned by the query.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Carlos Nunes-Ueno" <su******@NOSPAM.hotmail.com> wrote in message
news:33******************************@news.teranew s.com...
Hello all,

I'm building a database for a high school academic convention and the big
task for this DB is to sort out the contest winners.

Here's the tables (slightly simplified):

tblDelegate
-----------
DelID
LastName
FirstName
School
etc. . .

tblContest
----------
ContID
ContName
etc. . .

tblResult
---------
DelID_FK
ContID_FK
Result

My question is how do I query so that I get the top three places in each
contest, including ties. So if Bob, Sue, Adam, Cindy, and Joe each take
the test in Latin Grammar and the Scores come out 10, 10, 9, 8, and 7
respectively, then the results should be

1st: Bob and Sue
2nd: Adam
3rd: Cindy

with Joe not placing. Using the top predicate would return just the 10s
and the 9 so Bob or Sue would be returned as the 1st, 2nd would be the
other between the two and Adam would be third.

Does anyone any have idea of how to approach this?

Thanks,

-Carlos

Nov 12 '05 #3

P: n/a
Thanks Salad,

It works perfectly.

-Carlos

Salad <oi*@vinegar.com> wrote in
news:U6****************@newsread2.news.pas.earthli nk.net:
Maybe a mulipass query.

Create and follow what I do here to understand the concept. Cut/paste
the following SQL statements into new queries. You can adjust to your
situation once you follow/understand what I did.

I created a table called Table1. It has 2 fields; GroupID (similar to
the contest) and ScoreNum. I had 2 groups to test on, Group1 and
Group2. I added 6 records for group 1 with the scores 5,5,4,3,2,1. I
added 6 record for group 2 with the scores of 5,4,4,3,2,1.

I then created 2 queries; query1 and query2. Here is the SQL for
Query1
SELECT DISTINCT Table1.groupid, Table1.Scorenum
FROM Table1
ORDER BY Table1.groupid, Table1.Scorenum DESC;
This is similar to a totals record where I get 1 record for each score
for each group. Thus for 5,5,4,3,2,1 I end up with 5 records for
group1 or 5,4,3,2,1.

I then created the next query. Here's the SQL
SELECT Table1.groupid, Table1.Scorenum
FROM Table1
WHERE (((Table1.Scorenum) In (Select Top 3 ScoreNum From Query1
Where
GroupID = [GroupID])))
ORDER BY Table1.groupid;
I now know what the top 3 scores are for each group.

Now it is simply a matter of making a 3rd query where you link Query2
to the scores field in your table to be able to list the names of the
winners. Use this query to display your winners.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.