Query for winners

**Salad** · Nov 12 '05, 10:34 PM

Re: Query for winners

Carlos Nunes-Ueno wrote:
[color=blue]
> 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[/color]

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.Score num) 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.

**PC Datasheet** · Nov 12 '05, 10:34 PM

Re: Query for winners

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
resource@pcdata sheet.com

https://www.pcdatasheet.com

"Carlos Nunes-Ueno" <sullascl@NOSPA M.hotmail.com> wrote in message
news:3308fe2c45 be03d2a0ca3a772 0d79ae4@news.te ranews.com...[color=blue]
> 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[/color]

**Carlos Nunes-Ueno** · Nov 12 '05, 10:35 PM

Re: Query for winners

Thanks Salad,

It works perfectly.

-Carlos

Salad <oil@vinegar.co m> wrote in
news:U64ic.8390 $e4.7715@newsre ad2.news.pas.ea rthlink.net:
[color=blue]
> 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.Score num) 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.[/color]

Query for winners

Query for winners

Comment

Comment

Comment