SyntaxFix

[ms-access-2007] How to show row number in Access query like ROW_NUMBER in SQL

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;

Examples related to ms-access-2007

• C# Inserting Data from a form into an access Database • How to show row number in Access query like ROW_NUMBER in SQL • Import an Excel worksheet into Access using VBA • How to refer to Excel objects in Access VBA? • Retrieve column values of the selected row of a multicolumn Access listbox • Determine whether a Access checkbox is checked or not • Now() function with time trim • How do I correctly use "Not Equal" in MS Access? • A select query selecting a select statement • Use SELECT inside an UPDATE query

Examples related to row-number

• Trouble using ROW_NUMBER() OVER (PARTITION BY ...) • How to show row number in Access query like ROW_NUMBER in SQL • Oracle 'Partition By' and 'Row_Number' keyword • Select specific row from mysql table • MySQL - Get row number on select • ROW_NUMBER() in MySQL • How do I use ROW_NUMBER()? • Equivalent of Oracle's RowID in SQL Server • MSSQL Select statement with incremental integer column... not from a table • Why is there still a row limit in Microsoft Excel?