Free Lessons Courses Seminars TechHelp Fast Tips Templates Topic Index Forum ABCD

 Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum ------------ Excel Courses Excel Index ------------ Code Vault Testimonials

# Row Number in a Query

In this tutorial you will learn how to add a ROW number to a Query.

Take an example Table of some Names.

tblNames
IDFirstName
1Fred
3Barry
4Joan
5Ben
7Will

You wish to add the Row number beside them:

tblNames
Row #IDFirstName
11Fred
23Barry
34Joan
45Ben
57Will

There is no row_number like in SQL Server.

You can achive this in a number of ways:

RowNum: DCount("[ID]","[tblNames]","[ID]<=" & [ID])

See this FREE Tip on DCOUNT.

SELECT A.*,
(SELECT COUNT(*) FROM tblNames WHERE A.ID>=ID) AS RowNum
FROM tblNames AS A
ORDER BY A.ID;

These are not very efficient so don't use them on large recordsets.

An addition to this could be adding the Ordinal or Suffix of st, nd, rd or th

Just create a Function:

Function myOrdinal (D as Integer) as String
Dim Suffix as String
Select Case D
Case 1, 21, 31: Suffix = "st"
Case 2, 22: Suffix = "nd"
Case 3, 23: Suffix = "rd"
Case Else: Suffix = "th"
End Select
myOrdinal = D & Suffix
End Function

First shown at Forum.

 Check out these other pages that may be of interest to you:
 2/2/2023 Import Objects 2/1/2023 Continuous Forms Not Working 1/31/2023 Group On Top 1/30/2023 Association 8 1/27/2023 Math in Fields 1/26/2023 Association 7 1/25/2023 Change Query 1/24/2023 Association 6 1/23/2023 Association 5 1/20/2023 Association 4