How to show row number in Access query like ROW_NUMBER in SQL
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
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;