The Access database engine does not support the SQL Server ROW_NUMBER() window function. For modest local datasets, a correlated domain count can assign a deterministic position based on a sort value and a unique tie-breaker.
Last updated: September 26, 2026.
SELECT s.ScoreID,
s.TeamID,
s.Points,
1 + DCount("*", "Scores",
"TeamID=" & [TeamID] &
" AND (Points>" & [Points] &
" OR (Points=" & [Points] &
" AND ScoreID<" & [ScoreID] & "))") AS RowRank
FROM Scores AS s
ORDER BY s.TeamID, s.Points DESC, s.ScoreID;Within each team, higher points rank first. When points tie, the lower ScoreID comes first, so every row receives a stable position rather than an arbitrary number.
Decide whether you need rank or row number
A row number gives every record a unique position. A competition rank gives tied scores the same rank and leaves a gap afterward. To produce tied ranks, remove the ScoreID tie-breaker and count only rows with greater points. State the rule in the article, report, or field name so readers know how ties behave.
Keep the criteria type-safe
The example concatenates numeric values into the domain criterion. Text values require escaped quotes, and dates require Access date delimiters, which makes the expression harder to maintain. Prefer numeric surrogate keys for the tie-breaker. Null sort values need an explicit rule, such as excluding them or converting them with Nz.
DCount runs for every output row and can be slow on large linked tables. Index the group, sort, and primary-key fields. For larger datasets, calculate ranks in SQL Server, a pass-through query, or a temporary table.
Use the right neighboring pattern
If you only need the best few rows in every category, the existing top records per group pattern may be simpler. If you need cumulative money rather than position, use a running total query. Ranking and running totals look similar but answer different questions.
Outgrowing complex Access queries?
See how Access data and business workflows can move to a maintainable web application while preserving the underlying database logic. Read the migration guide.
