Positions 1st, 2nd, 3rd etc

[Peter Mitchell]

Haviong sorted records in a query how do I allocate
highest score 1st
second highest 2nd
third highest 3rd
etc

the purpose could be students results for an exam for example.

Hope someone can help, please.

Peter

 

[Marshall Barton]

Haviong sorted records in a query how do I allocate
highest score 1st
second highest 2nd
third highest 3rd
etc

the purpose could be students results for an exam for example.

When you have a querstion about a query, you should post a
Copy/Paste of the query's SQL view so we can see what you
have. With out that, all we can do is post a generic kind
of reply that leaves you guessing as to how it relates to
your situation.

SELECT T.f1, T.f2, . . .,
(SELECT Count(*)
FROM table As X
WHERE X.score <= T.score
) As Rank
FROM table As T

 

[Peter Mitchell]

Clearly the "AS RANK" is a key part of what I need - but I am confused what
I put for [YourTable-4] and T1.

My table name is Students with two fields StudentID and Score so I have the
following query (saved as Ordered) to put the scores in order.

SELECT Students.StudentID, Students.Score
FROM Students
ORDER BY Students.Score DESC;

While I am quite familiar with SQL the "AS" on the FROM line is new - and I
haven't seen RANK before.

I hope this is sufficient information for you to be able to assit me
further. Thank you in anticipation.

Peter Mitchell

 

[Marshall Barton]

In the query I posted:

SELECT T.f1, T.f2, . . .,
(SELECT Count(*)
FROM table As X
WHERE X.score <= T.score
) As Rank
FROM table As T

the As in the FROM clauses assign an alias for the table
name.

The As T in the outer select statment is just a convenience
so I don't have to type the full table name all over the
place. It also helps you because you only have to
substitute your real table name in one place.

On the other hand the As X in the subquery is necessary so
you can distinguish which instance of the score field you
are comparing to the other instance.

The As Rank is the name of the calculated field determined
by the subquery.

Most of that is explained in both Access and VBA Help -
Table of Contents - Microsoft Jet SQL Reference - Data
Manipulation Language

I did not use [YourTable-4] and T1 so I have no idea what
you are referring to with that.

Now that you have posted your table and field names, I can
translate the generic query to your specific situation:

SELECT Students.StudentID, Students.Score,
(SELECT Count(*)
FROM Students As X
WHERE X.Score <= Students.Score
) As Rank
FROM Students
ORDER BY Students.Score DESC
--
Marsh
MVP [MS Access]

Clearly the "AS RANK" is a key part of what I need - but I am confused what
I put for [YourTable-4] and T1.

My table name is Students with two fields StudentID and Score so I have the
following query (saved as Ordered) to put the scores in order.

SELECT Students.StudentID, Students.Score
FROM Students
ORDER BY Students.Score DESC;

While I am quite familiar with SQL the "AS" on the FROM line is new - and I
haven't seen RANK before.

Haviong sorted records in a query how do I allocate
highest score 1st
second highest 2nd
third highest 3rd
etc

the purpose could be students results for an exam for example.

 

[Jamie Collins]

Now that you have posted your table and field names, I can
translate the generic query to your specific situation

Adding some final polish:

SELECT DT1.StudentID, DT1.Score,
DT1.Rank &
SWITCH(
RIGHT$(DT1.Rank, 1) = '1' AND RIGHT$(DT1.Rank, 2) <> '11', 'st',
RIGHT$(DT1.Rank, 1) = '2' AND RIGHT$(DT1.Rank, 2) <> '12', 'nd',
RIGHT$(DT1.Rank, 1) = '3' AND RIGHT$(DT1.Rank, 2) <> '13', 'rd',
TRUE, 'th') AS place
FROM
(
SELECT Students.StudentID, Students.Score,
(SELECT Count(*)
FROM Students As X
WHERE X.Score >= Students.Score
) As Rank
FROM Students
) AS DT1
ORDER BY DT1.Rank