16

I have a table in Microsoft Access, and I want to show row number in a column using a select query in Access just like using ROW_NUMBER() function in SQL Server.

In SQL Server, I can using this query:

SELECT ROW_NUMBER() OVER (ORDER BY tblUser.UserID) AS NoRow, * 
FROM tblUser

I use same query in access, but I get error.

Can you help me?

shA.t
  • 16,580
  • 5
  • 54
  • 111
Fauzi88
  • 713
  • 3
  • 11
  • 23
  • 1
    Here are a few [**Ways To Do Sequential Numbering in Access**](https://accessexperts.com/blog/2015/07/28/ways-to-do-sequential-numbering-in-access/). – ashleedawg Sep 05 '18 at 18:08

3 Answers3

28

You can try this query:

Select A.*, (select count(*) from Table1 where A.ID>=ID) as RowNo
from Table1 as A
order by A.ID
JokoSumanto
  • 466
  • 4
  • 7
4

One way to do this with MS Access is with a subquery but it does not have anything like the same functionality:

SELECT a.ID, 
       a.AText, 
       (SELECT Count(ID) 
        FROM table1 b WHERE b.ID <= a.ID 
        AND b.AText Like "*a*") AS RowNo
FROM Table1 AS a
WHERE a.AText Like "*a*"
ORDER BY a.ID;
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • I don't understand with where condition (AND b.AText Like "*a*") and (a.AText Like "*a*") – Fauzi88 Jul 19 '13 at 09:03
  • It's just to indicate that whatever WHERE statements you'll need in your outer query must be copied to the inner one as well. – Adamantish Mar 19 '14 at 14:52
1

by VB function:

Dim m_RowNr(3) as Variant
'
Function RowNr(ByVal strQName As String, ByVal vUniqValue) As Long
' m_RowNr(3)
' 0 - Nr
' 1 - Query Name
' 2 - last date_time
' 3 - UniqValue

If Not m_RowNr(1) = strQName Then
  m_RowNr(0) = 1
  m_RowNr(1) = strQName
ElseIf DateDiff("s", m_RowNr(2), Now) > 9 Then
  m_RowNr(0) = 1
ElseIf Not m_RowNr(3) = vUniqValue Then
  m_RowNr(0) = m_RowNr(0) + 1
End If

m_RowNr(2) = Now
m_RowNr(3) = vUniqValue
RowNr = m_RowNr(0)

End Function

Usage(without sorting option):

SELECT RowNr('title_of_query_or_any_unique_text',A.id) as Nr,A.*
From table A
Order By A.id

if sorting required or multiple tables join then create intermediate table:

 SELECT RowNr('title_of_query_or_any_unique_text',A.id) as Nr,A.*
 INTO table_with_Nr
 From table A
 Order By A.id
MartiniB
  • 31
  • 5