Assign values based on Count(field_name) in query

[RBohannon]

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.

[James Fortune]

ra*****@hotmail.com (RBohannon) wrote in message news:<ad*************************@posting.google.c om>...

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

[RBohannon]

ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...

ra*****@hotmail.com (RBohannon) wrote in message news:<ad*************************@posting.google.c om>...

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.