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 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.
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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"...
|
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...
|
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...
|
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...
|
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):
...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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...
|
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...
| |