473,379 Members | 1,250 Online

Assign values based on Count(field_name) in query

I'm using Access 2000 on Windows 2000.

I have a DB of several exams and people who have taken them. I need
to assign ranks to the people according to their scores (within each
exam).

I have a select query written like this:
SELECT[list].[Score], Count([list].[Score]) AS Tally,[list].[Exam_Number]
FROM[list]
GROUP BY[list].[Score],[list].[Exam_Number]
HAVING ((([list].[Exam_Number])=[Enter Exam Number]))
ORDER BY[list].[Score] DESC;
So if the user were to enter "12345" as the Exam Number, the result
might look like this:
Score Tally Exam_Number
100 8 12345
95 2 12345
90 63 12345
85 2 12345
80 51 12345
70 29 12345

The ranking would be determined as follows:
All people with the highest score are ranked 1. In this example, 8
people are ranked 1 (100 would not necessarily be the highest score.)
The rank for the next lowest score is figured by adding the previous
rank number (1) to the number of people in that rank (8).
Thus, the 2 people with a score of 95 are ranked 9.
Then 9 + 2 = 11, so the 63 people with a score of 90 are ranked 11,
etc.

So a table of ranks for this exam would look like this:
Score Tally Rank
100 8 1
95 2 9
90 63 11
85 2 74
80 51 76
70 29 127

Ultimately a rank needs to be assigned to each person based on their
score.

What would be the best way to create this table of ranks and/or make
the rank assignment to each person?

Any assistance is appreciated.
Nov 13 '05 #1
2 2449
I'm using Access 2000 on Windows 2000.

I have a DB of several exams and people who have taken them. I need
to assign ranks to the people according to their scores (within each
exam).

I have a select query written like this:
SELECT[list].[Score], Count([list].[Score]) AS Tally,
[list].[Exam_Number]
FROM[list]
GROUP BY[list].[Score],[list].[Exam_Number]
HAVING ((([list].[Exam_Number])=[Enter Exam Number]))
ORDER BY[list].[Score] DESC;
So if the user were to enter "12345" as the Exam Number, the result
might look like this:
Score Tally Exam_Number
100 8 12345
95 2 12345
90 63 12345
85 2 12345
80 51 12345
70 29 12345

The ranking would be determined as follows:
All people with the highest score are ranked 1. In this example, 8
people are ranked 1 (100 would not necessarily be the highest score.)
The rank for the next lowest score is figured by adding the previous
rank number (1) to the number of people in that rank (8).
Thus, the 2 people with a score of 95 are ranked 9.
Then 9 + 2 = 11, so the 63 people with a score of 90 are ranked 11,
etc.

So a table of ranks for this exam would look like this:
Score Tally Rank
100 8 1
95 2 9
90 63 11
85 2 74
80 51 76
70 29 127

Ultimately a rank needs to be assigned to each person based on their
score.

What would be the best way to create this table of ranks and/or make
the rank assignment to each person?

Any assistance is appreciated.

By prepending a row to your results (can be done with a Union Query):

Score Tally ExamNumber
110 0 12345
100 8 12345
....

And running:
SELECT tblList.Score, tblList.Tally, tblList.ExamNumber, (SELECT
SUM([Tally]) + 1 FROM tblList As GetRank WHERE tblList.Score <
GetRank.Score) AS Rank FROM tblList WHERE (Not (SELECT SUM([Tally]) +
1 FROM tblList As GetRank WHERE tblList.Score < GetRank.Score) Is
Null);

I got:
Score Tally ExamNumber Rank
100 8 12345 1
95 2 12345 9
90 63 12345 11
85 2 12345 74
80 51 12345 76
70 29 12345 127

I'm sure there's a technique buried in there somewhere for solving
this common problem. It can probably be done with a single SQL
statement.

James A. Fortune
Nov 13 '05 #2
ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...
I'm using Access 2000 on Windows 2000.

I have a DB of several exams and people who have taken them. I need
to assign ranks to the people according to their scores (within each
exam).

I have a select query written like this:
SELECT[list].[Score], Count([list].[Score]) AS Tally,
[list].[Exam_Number]
FROM[list]
GROUP BY[list].[Score],[list].[Exam_Number]
HAVING ((([list].[Exam_Number])=[Enter Exam Number]))
ORDER BY[list].[Score] DESC;
So if the user were to enter "12345" as the Exam Number, the result
might look like this:
Score Tally Exam_Number
100 8 12345
95 2 12345
90 63 12345
85 2 12345
80 51 12345
70 29 12345

The ranking would be determined as follows:
All people with the highest score are ranked 1. In this example, 8
people are ranked 1 (100 would not necessarily be the highest score.)
The rank for the next lowest score is figured by adding the previous
rank number (1) to the number of people in that rank (8).
Thus, the 2 people with a score of 95 are ranked 9.
Then 9 + 2 = 11, so the 63 people with a score of 90 are ranked 11,
etc.

So a table of ranks for this exam would look like this:
Score Tally Rank
100 8 1
95 2 9
90 63 11
85 2 74
80 51 76
70 29 127

Ultimately a rank needs to be assigned to each person based on their
score.

What would be the best way to create this table of ranks and/or make
the rank assignment to each person?

Any assistance is appreciated.

By prepending a row to your results (can be done with a Union Query):

Score Tally ExamNumber
110 0 12345
100 8 12345
...

And running:
SELECT tblList.Score, tblList.Tally, tblList.ExamNumber, (SELECT
SUM([Tally]) + 1 FROM tblList As GetRank WHERE tblList.Score <
GetRank.Score) AS Rank FROM tblList WHERE (Not (SELECT SUM([Tally]) +
1 FROM tblList As GetRank WHERE tblList.Score < GetRank.Score) Is
Null);

I got:
Score Tally ExamNumber Rank
100 8 12345 1
95 2 12345 9
90 63 12345 11
85 2 12345 74
80 51 12345 76
70 29 12345 127

I'm sure there's a technique buried in there somewhere for solving
this common problem. It can probably be done with a single SQL
statement.

James A. Fortune

Thanks for the help.

Writing code to do this in VB is a simple matter, but I'm not very
experienced with VBA and SQL. I tried using the results of the query
in VBA, but I'm having some difficulty with the proper references to
the database object, etc. For example, I wrote Dim dbs As Database
(like in the help file example) and I got an error message saying
"Database" was an undefined user-defined type.

So if I can do this in SQL, it may be easier for me now.
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

 2 by: Alex | last post by: Subject: Looking for an XML (database-based) Query Reporting Tool/advice First off, let me apologize if this thread is somewhat off topic... PLEASE REPLY TO: xml@solex-bi.com I am looking... 6 by: Nicolae Fieraru | last post by: Hi All, I have a query, Select Count(BoolField) from tblMyTable, Where BoolField = true. If I run the query by itself, it returns the number of true records I want to use the result of that... 12 by: briansmccabe | last post by: Does anyone have a good approach to displaying in PHP a simple COUNT query that is performed on a table in a MySQL db? Thanks 6 by: dBNovice | last post by: Hey group, I am trying to do a count of the number of papers in a table. The table has a PaperID that differentiates each paper , e.g. 004.1. Some papers are reused. The reused paper is given... 2 by: cwhite | last post by: I'm having problems with a form based query The user makes a selection from a drop box, there are only two choices: Current Former the user makes a choice and clicks the preview report... 2 by: MLH | last post by: Have a data entry form bound to a table. Has a save button on it that launches DoCmd.RunCommand acCmdSaveRecord in Access 97. In the form after-update code, I have a line looking something like... 1 by: lwwhite | last post by: When a user clicks OK on form "SelectDefaults," I want to open form "Welcome" if the results of query "qry_todo_overdue" = 0 or form "OverdueToDo" if the results >= 1. First, I assume that I need to... 3 by: WyvsEyeView | last post by: This seems like it should be so easy to do. I have a table, called tblTopics. Each topic can have one or more instances, contained in a table called tblTopicInst. tblTopics is bound to a form called... 1 by: MikeMikerson | last post by: Hello, I am need to create a subform (no problem) of a form based query (a problem). I need a form that will prompt the user to enter in a name, and the form will then display a query of the... 1 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: ryjfgjl | last post by: In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import... 0 by: Charles Arthur | last post by: How do i turn on java script on a villaon, callus and itel keypad mobile phone 0 by: aa123db | last post by: Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function \$name\$ (\$parameters\$) { } ... 0 by: ryjfgjl | last post by: If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming... 0 by: emmanuelkatto | last post by: Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel 0 by: BarryA | last post by: What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress... 1 by: Sonnysonu | last post by: This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to... 0 by: Hystou | last post by: There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...