 Row Number in a QueryAdding 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
|
Comments
Post a Comment