How to Add Sequential Numbers by Group in a Microsoft Access Query
Question details
The user needs to create a running sequence number that restarts for each batch_num group in Microsoft Access, using a DAO recordset, DCount update query, or similar method when the table lacks a unique key.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- The user is working with an Access table or query that contains batch records and needs a seq_num field populated with 1,2,3 within each batch_num group.
- Observed behavior
- The table may not have a unique key, so a simple query does not provide a consistent group-based sequence and some seq_num values remain null.
Confirm the target table, the batch_num field that defines each group, and the seq_num field that will store the sequence values. Also identify a unique field or sort order so the DAO recordset or DCount expression can number rows consistently within each group.
Use a DAO Recordset to Assign Sequential Numbers by Group in Access
Recommended when the table lacks a reliable unique key or when you need to update only null seq_num values and reset the counter each time batch_num changes.
In Microsoft Access, press Alt+F11 to open the Visual Basic for Applications editor, then insert a new standard module from the Insert menu.
Declare a Database and Recordset object, then open the target table as a recordset sorted by batch_num and a unique or order field such as ID.
Loop through the recordset and store the previous batch_num value. When the current batch_num is different from the previous one, reset the sequence counter to 1.
For each row where seq_num is null, assign the current counter value, call the Update method to save the change, and then increment the counter.
Run the subroutine from the Immediate Window or a macro/button, then open the table to verify that each batch_num group is numbered sequentially.

Use a DCount Expression in an Access Update Query
Useful for smaller tables or one-time updates when the table has a unique field such as ID to order rows within each batch_num group.
Use WPS Office Free for Spreadsheet and Document Work
WPS Office does not replace the Microsoft Access database engine, but it is a free, lightweight Office alternative that opens and saves Microsoft Excel, Word, and PowerPoint files with a familiar ribbon interface. You can move grouped numbering work into WPS Spreadsheets and use formulas instead of Access queries.
- 1. Download WPS Office Free: Visit the official WPS Office download page and install WPS Office on your Windows or Mac computer.
- 2. Open your exported Excel file: If you have exported Access data to Excel, open the workbook in WPS Spreadsheets to continue working with grouped records.
- 3. Use a grouped sequence formula: In WPS Spreadsheets, use a formula such as =COUNTIFS($A$2:A2,A2) in a helper column to number rows within each batch group.

Frequently Asked Questions
Does WPS Office include an Access database replacement?
No, WPS Office does not include a database application equivalent to Microsoft Access. It includes Writer, Spreadsheet, Presentation, and PDF tools, and it can open Excel files for spreadsheet-based work.
Can I use the DCount expression in WPS Office?
No, DCount is an Access-specific domain function and is not available in WPS Office. In WPS Spreadsheets, you can use functions such as COUNTIFS to create grouped sequence numbers.
Will the DAO recordset method work if my Access table has no primary key?
It can work if you sort the recordset by batch_num and another field that provides a consistent row order. Without a reliable sort order, the assigned sequence may not be stable.
Why does the DCount update query become slow on large tables?
DCount recalculates the domain expression for each row, so performance can degrade as the table grows. A DAO recordset is usually faster because it reads the table once in order.




