How Do I Sequentially Number Records in an Access Query?

By Lysis

Updated September 28, 2017

Access is a database used for desktop applications.
i laptop with database record on 15.4" wide screen image by .shock from Fotolia.com

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.

×