How to Do Ranking with Positioning (1st, 2nd, 3rd, …) in MS Access – Step-by-Step Guide

Do you want to assign ranking positions like 1st, 2nd, 3rd, in your MS Access reports or queries? Whether you're working with student grades, sales performance, or any competitive data, ranking with ordinal numbers makes your reports more readable and professional.

In this blog, I’ll show you how to implement ranking with positioning in Microsoft Access using Queries and expressions. It’s simple, flexible, and works even with ties in value!

What You’ll Learn

• How to rank data in MS Access using Query expression
• How to assign ordinal suffixes (1st, 2nd, 3rd, etc.)
• How to display ranked results in a report or query

Step 1: Create the Score Table

Open your MS Access database and create a new table with the following fields:

• StudentID
• StudentName
• TotalScore

Save the table as tblScores. As in the picture below

Step 2: Create a Query for the tblScores as QueryScore.

Step 3: Create a Ranking Query Using DCount

Use the DCount function to count how many students scored higher than each student:

This gives each student a rank based on their score.

What’s happening?

Rank:1 + DCount("*", "tblScores", "TotalScore > " & S.TotalScore) counts how many scores are higher.

Adding 1 gives the current rank.

Step 4: Add Ordinal Suffix (1st, 2nd, 3rd…) As Position

Use a calculated field with the Switch function to add ordinal suffixes to the ranks: 

Switch function

Position: Switch(Right([Rank],2) In ("11","12","13"),[Rank] & "th",Right([Rank],1)="1",[Rank] & "st",Right([Rank],1)="2",[Rank] & "nd",Right([Rank],1)="3",[Rank] & "rd",True,[Rank] & "th")

Step 5: Use It in a Report or Form

• Set your record source to the ranking query.
• Add a text box bound to the Position field.
• Sort the report by Rank to display top scores first.

Final Output

Final Thoughts

Ranking in MS Access is powerful and easy when you know how to use DCount and Switch. This approach gives your users a familiar, friendly display like "1st", "2nd", "3rd"—and it's great for reports and dashboards. Download file