How Do I Sequentially Number Records in an Access Query?
By Lysis
Updated September 28, 2017
If you're creating an Access query on a table that does not have sequential numbers, you can add them to the query manually using the "Dcount()" function. The Dcount function uses the row number and adds it to the query, so you can order and list the results in your Microsoft Access reports and web pages. The Dcount function is added in the query design view, so there is a limited amount of code required from the programmer.
Open Microsoft Access and load the database with the query you want to edit. Click the "Queries" icon at the main menu to open a list of queries programmed in the database.
Right click the query you want to edit and select "Design View." This opens the query editor in design view, so you can view the tables and fields selected.
Enter a new field in the list at the bottom of the screen. Use the following code to add a sequential number to the query results:
DCount("id","myTable","id <= " & [id]) AS Counter
Replace "myTable" with the name of the table used in the query.
Save the query and close the design viewer. Double click the query name, which runs it instead of opening it for design. You will now see a new field called "Counter," which shows a sequential number for each row.
References
Writer Bio
Lysis is the pen name for a former computer programmer and network administrator who now studies biochemistry and biology while ghostwriting for clients. She currently studies health, medicine and autoimmune disorders. Lysis is currently pursuing a Ph.D. in genetic engineering.