RANK in MS Access

SMAN 15 Bandar Lampung
0
Sometimes one needs to rank data based on some field e.g. Students’ Marks, Product Sales etc. In different platforms there are different ways to do it.
In SQL Server and Excel there is RANK function that does the job but in MS Access there is no such built-in function.
However same result can be achieved using a correlated query.
e.g. Consider a dataset of student marks below.
Students Marks
Tony 34
Bob 32
Thor 48
Jack 42
Tom 41
Kate 45
Sid 26
Suppose one wants to rank students based on their marks (rank = 1 for highest marks). Following query can be used to rank them.

SELECT Students,
       (SELECT COUNT(T1.Marks)
          FROM
                 [Table] AS T1
         WHERE T1.Marks >= T2.Marks) AS Rank
FROM
      [Table] AS T2
ORDER BY Marks DESC

Following is the result of the query.

Students Rank
Thor 1
Kate 2
Jack 3
Tom 4
Tony 5
Bob 6
Sid 7
If,  rank 1 is to be assigned to the lowest value, then in that case the query above can be slightly modified to:

SELECT Students,
       (SELECT COUNT(T1.Marks) 
          FROM
                 [Table] AS T1 
         WHERE T1.Marks <= T2.Marks) AS Rank 
FROM 
      [Table] AS T2 
ORDER BY Marks 

sumber : https://usefulgyaan.wordpress.com/2013/04/23/ranking-in-ms-access/
Tags

Posting Komentar

0Komentar
Posting Komentar (0)