Tips & Tricks MS Access Row Number in a Query

Tips & Tricks New Tips Added Weekly! Click here to get on our Mailing List       Access   Excel   Word   Windows   FrontPage   Hardware   Misc   VB   VBScript   VB.NET   ASP   HTA     Row Number in a Query Adding a Row Number in your Query. In this tutorial you will learn how to add a ROW number to a Query. Take an example Table of some Names. tblNames ID FirstName 1 Fred 3 Barry 4 Joan 5 Ben 7 Will You wish to add the Row number beside them: tblNames Row # ID FirstName 1 1 Fred 2 3 Barry 3 4 Joan 4 5 Ben 5 7 Will There is no row_number like in SQL Server. You can achive this in a number of ways: ? 1 2 RowNum: DCount("[ID]","[tblNames]","[ID]<=" & [ID]) See this FREE Tip on DCOUNT. ? 1 2 3 4 5 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: ? 1 2 3 4 5 6 7 8 9 10 11 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.   Courtusy:  By: Alex Hedley   You may want to read these articles from the 599CD News: 10/22/2020 ABCD Part 2: Core Database 10/21/2020 Consolidate Tables with Helper Data 10/20/2020 Is Microsoft Access Going Away? 10/12/2020 Store Multiple Phone Numbers 10/10/2020 ABCD Part 1: Core Database 10/10/2020 Create Your Own Custom Function 10/8/2020 Update Query from Another Table 10/5/2020 Access Business & Contact Database 10/5/2020 How and Why to Split Your Database 10/2/2020 Multiple Nested Continuous Subforms