Rank Scoring

[Guest]

I need to sort a table and assign a score to the first place (fastest time).
The first place would be a value of 1 and slower times would be 2, 3, 4, etc.
I then need to record the time and this new value to compile a rank score. Is
there an easy way?

Here is what I want:

876543 2.45 1
234564 2.876 2
654789 3.56 3

This would be for test one. There are a total of 15 tests. A student may not
always be first but we need to find his total rank score.

654789 2.45 1
234564 2.876 2
876543 3.56 3

Here is the results for test two

Here are the rank scoring for all three

876543 1 + 3 = 4
234564 2 + 2 = 4
654789 3 + 1 = 4

This is the way the district is scoring a test we give every year and I am
hoping I do not have to hand calculate the rank score by hand anymore. I can
get the sorting to show me the order but with 15 tests and as many as 300
students there is just a lot of hand work.

 

[Ken Snell [MVP]]

Let me give you an example of how to get a ranking from data. You then
should be able to modify this query and copy it for other rankings. You then
can create a query that would add the rankings from each ranking query.

To get a ranking of data in a table, where the rank is 1 for fastest time:

SELECT T.StudentID, T.TestTime,
(SELECT Count(Q.TestTime) FROM
Tablename AS Q WHERE
Q.TestTime <= T.TestTime) AS TestRank
FROM Tablename AS T
ORDER BY TestRank;

Post back if you have additional questions.

 

[Guest]

I am not sure what you are saying. I am am new to Access. I understand some
of the basics. Are you doing this in SQL commands?

 

[Ken Snell [MVP]]

What I posted is an example of an SQL statement, which is a query. You would
create a query using that SQL (create query in design view, close the "Add
Table" window, click on SQL icon at top left of toolbar, and paste the
statement in the window -- changing the names of the fields and the tables
to your real names).