Others

How to Add Sequential Numbers by Group in a Microsoft Access Query

Huma Ashraf ChHuma Ashraf Ch Nov 13, 2026 869 views

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.

How to Add Sequential Numbers by Group in a Microsoft Access Query
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the Visual Basic Editor

In Microsoft Access, press Alt+F11 to open the Visual Basic for Applications editor, then insert a new standard module from the Insert menu.

2
Create the numbering subroutine

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.

3
Loop and reset the counter

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.

4
Update only null seq_num values

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.

5
Run the procedure

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 DAO Recordset to Assign Sequential Numbers by Group in Access
Reliable grouped numbering: The DAO method gives you precise control over row order and counter resets, and it avoids the performance issues of DCount on large tables.
Free Microsoft Office alternative

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. 1. Download WPS Office Free: Visit the official WPS Office download page and install WPS Office on your Windows or Mac computer.
  2. 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. 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.
Free Office suite with Writer, Spreadsheet, and Presentation toolsOpens and saves Microsoft Excel, Word, and PowerPoint formats for smooth file compatibilityFamiliar ribbon UI reduces migration effort from Microsoft OfficeWPS Spreadsheets can handle grouped sequence numbering with formulas such as COUNTIFS
microsoft office alternative - wps office

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.