RANK in MS Access

Posted: April 23, 2013 by Transformer in MS Access, SQL
Tags: , , ,

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 

Advertisements
Comments
  1. […] another example of Correlated query, check this […]

  2. Gauri says:

    If two people have same marks it starts assigning ranks from 2 , what can be done to stil start it from 1 (may be considering the alphabetical order)

  3. Transformer says:

    It can be done like this:

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

  4. hier klicken says:

    Thanks for finally writing about >RANK in MS Access | Useful Gyaan <Liked it!

  5. […] these, and created the ranking query for each record. ACC2000: How to Rank Records Within a Query RANK in MS Access | Useful Gyaan Then, I created the ranking query using your data (assume table name was Table1): […]

  6. Phil says:

    I’ve been looking for ages on how to rank in Access! I have a Query (Query2) where I want to Rank 2 Cols. One is titled CourseID and the 2nd is titled Final Rating. CourseID may have say 10 rows with the same value lets say 201250 and Final Rating will have various values with some identical. I need to Rank the values in Final Rating from 1-10 but not starting at 2 for duplicates. Could I get the complete Dummies guide x 10 on how to to input this please? And I do mean DUMMIES 🙂

  7. vincent sichilaba says:

    Good afternoon

    Please lam working on the similar query. I would like like to find out where l should type the expression in the Desion View: SELECT Students, (SELECT COUNT(T1.Marks) FROM [test] AS T1 WHERE T1.Marks >= 2.Marks) AS Rank FROM [test] AS T2 ORDER BY Marks DESC

    Your help will be apprecaited

  8. vincent sichilaba says:

    Decision making

    I am using Access 2013.

    Can someone help out there!

    I have already set up a table with the following field name:

    TotalMark
    Result
    Comment

    Now in the query design view how do l set up queries that will perform the following:

    When the TotalMark is between 0 and 39, Result should read G, Comment should read fail
    When the TotalMark is between 40 and 49, Result should read F, Comment should read More Effort
    When the TotalMark is between 50 and 59, Result should read E, Comment should read Can do better
    When the TotalMark is between 60 and 69, Result should read D, Comment should read Good
    When the TotalMark is between 70 and 79, Result should read C, Comment should read well done
    When the TotalMark is between 80 and 89, Result should read B, Comment should read Brilliant
    When the TotalMark is between 90 and 100, Result should read A, Comment should read Excellent

    Thank you

    • Transformer says:

      Hi Vincent,
      You can do it in two ways
      1. If you are happy to set up a new mapping table for Grades bottom and top:
      e.g.
      Bottom Top Grade
      0 39 G
      40 49 F
      50 59 E
      60 69 D
      70 79 C
      80 89 B
      90 100 A

      Lets call it Mapping Table and DataTable to your main table.
      Query:

      SELECT T1.TotalMark,T2.Grade
      FROM
      DataTable as T1 INNER JOIN Mapping Table as T2
      ON T1.TotalMark>= T2.Bottom AND T1.TotalMark<= T2.TOP

      2. If you dont want to create a mapping table then you can use IIF statements to assign the grades like below:

      SELECT TotalMark,
      IIF(TotalMark<40,"G",IIF(TotalMark>39 AND TotalMark<49,"F",...so on) as Grades FROM DataTable

  9. George says:

    Hello

    Thanks so much for your post.
    Please I want to Rank multiple fields within one table. please can someone help me with how to go by that?

  10. Michael says:

    I am having the same issue on ranking, I have tried the above statement to no avail can somoene please assist?

  11. iamAwal says:

    I have a table called ExamsDetails with fields;

    ID, StudentID, ClassName, SubjectID, Term, ClassScore, ExamsScore

    > ID is an autonumber field
    > StudentID a foreign key from Students Table i.e a unique ID for each student . EG. S20120005

    >ClassName is a text field indicating the respective class the student is in.

    E.g:Sc1A ,SC1B,AG1A, AG1B ,SC2A etc.

    SubjectID is a numeric field. A foreign key from Subjects Table indicating the subject . eg. English, Mathermatics etc

    Term is a numeric filed indicating the academic session i.e 1,2 or 3

    I created a query from ExamsDetails Table called ExamsScores with fields:

    StudentID,ClassName, SubjectID, Term, TotalScore and Rank

    ClassName and Term are parameter fields from a form that filter the desired class and Term.

    TotalScore is a calculated field that calculates the total score obtained from ClassScore and ExamScore.

    I would like to Rank the student Position based on the SubjectID as a group using TotalScore obtained by the Student.

    That is Taking a Subject, Rank the Students all in the Class. Then Rank the Students again taking the Next Subject upto the Last Subject.

    Hope this is clear enough. Thank you.

  12. Neil Watters says:

    I’m in supply chain and I had to create stages for shipments (stop 1, stop 2, etc) based on a max of order count (itinerary). What that means is that if a shipment had 3 stops there could be 8 orders on that shipment so orders 1 & 2 would be on stop 1, 3 thru 6 on stop 2, 7 & 8 on stop 3. So with the 3 stops I would have max of order numbers (itinerary) 2, 6, and 8 and I have to convert those to 1, 2, and 3. This is the query I used in Access (wrote it in VBA, not sure how to do it in the query editor)…

    It creates a new table that I can do a quick update query on

    SELECT a.[Shipment], a.[Customer], a.[Itinerary] ,
    (SELECT Count(*) As Rank FROM
    (SELECT [Shipment],[Itinerary] FROM [Shipment] b) as Alias1
    WHERE Alias1.[Shipment] = a.[Shipment] AND Alias1.[Itinerary] <= a.[Itinerary]) as CountS
    INTO [TestRank]
    FROM [Shipment] a

    Just thought I'd share

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s