473,320 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query for winners

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
3 2229
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: jaysonsch | last post by:
Hello! I am having some problems with a database query that I am trying to do. I am trying to develop a way to search a database for an entry and then edit the existing values. Upon submit, the...
29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
3
by: Harvey | last post by:
Hi, I try to write an asp query form that lets client search any text-string and display all pages in my web server that contain the text. I have IIS 6.0 on a server 2003. The MSDN site says...
4
by: Diamondback | last post by:
I have two tables, WIDGETS and VERSIONS. The WIDGETS table has descriptive information about the widgets while the VERSIONS table contains IDs relating to different iterations of those widgets...
3
by: Carlos Nunes-Ueno | last post by:
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): ...
7
by: worldbadger | last post by:
Hello, We had a competition of guessing the M&M's in 25 different jars. The database has a name, guess, date and time field in it (as well as the ID field). What I need to do now is get the...
2
by: Jedi Knight | last post by:
Hi, I think I need to use an EXISTS query, but I am not vey proficient with it and don't quite have a handle on the logic of this query yet. query1, winners, has 3 fields that I'm concerend...
28
by: G8tors | last post by:
I have a fantasy football league that I am keeping stats for in an Access Database. I need help coming up with a way to calclulate the winning % for each team for their entire career. What I have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.