Row numbers in query result using Microsoft Access
Since I am sorting alphabetically on a string field and NOT by ID, the Count(*) and DCOUNT() approaches didn't work for me. My solution was to write a function that returns the Row Number:
Option Compare Database
Option Explicit
Private Rst As Recordset
Public Function GetRowNum(ID As Long) As Long
If Rst Is Nothing Then
Set Rst = CurrentDb.OpenRecordset("SELECT ID FROM FileList ORDER BY RealName")
End If
Rst.FindFirst "ID=" & ID
GetRowNum = Rst.AbsolutePosition + 1
' Release the Rst 1 sec after it's last use
'------------------------------------------
SetTimer Application.hWndAccessApp, 1, 1000, AddressOf ReleaseRst
End Function
Private Sub ReleaseRst(ByVal hWnd As LongPtr, ByVal uMsg As Long, ByVal nIDEEvent As Long, ByVal dwTime As Long)
KillTimer Application.hWndAccessApp, 1
Set Rst = Nothing
End Sub
Another way to assign a row number in a query is to use the DCount
function.
SELECT *, DCount("[ID]","[mytable]","[ID]<=" & [ID]) AS row_id
FROM [mytable]
WHERE row_id=15
MS-Access doesn't support ROW_NUMBER(). Use TOP 1:
SELECT TOP 1 *
FROM [MyTable]
ORDER BY [MyIdentityCOlumn]
If you need the 15th row - MS-Access has no simple, built-in, way to do this. You can simulate the rownumber by using reverse nested ordering to get this:
SELECT TOP 1 *
FROM (
SELECT TOP 15 *
FROM [MyTable]
ORDER BY [MyIdentityColumn] ) t
ORDER BY [MyIdentityColumn] DESC
Though this is an old question, this has worked for me, but I've never tested its efficiency...
SELECT
(SELECT COUNT(t1.SourceID)
FROM [SourceTable] t1
WHERE t1.SourceID<t2.SourceID) AS RowID,
t2.field2,
t2.field3,
t2.field4,
t2.field5
FROM
SourceTable AS t2
ORDER BY
t2.SourceID;
Some advantages of this method:
- It doesn't rely on the order of the table, either - the
RowID
is calculated on its actual value and those that are less than it. - This method can be applied to any (primary key) type (e.g.
Number
,String
orDate
). - This method is fairly SQL agnostic, or requires very little adaptation.
Final Thoughts
Though this will work with practically any data type, I must emphasise that, for some, it may create other problems. For instance, with strings, consider:
ID Description ROWID
aaa Aardvark 1
bbb Bear 2
ccc Canary 3
If I were to insert: bba Boar
, then the Canary
RowID
will change...
ID Description ROWID
aaa Aardvark 1
bbb Bear 2
bba Boar 3
ccc Canary 4