How to Assign Sequential Occurrence Numbers to Duplicates in Microsoft Access
Question details
The user needs to assign sequential occurrence numbers separately for each duplicate customer group in Microsoft Access, such as 1, 2, 3 for the first customer and 1, 2 for the second customer.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Combining or analyzing records with repeated customer values where each customer group must be numbered independently.
- Observed behavior
- Each duplicate customer value should receive its own sequence starting at 1 according to a defined sort order.
Identify the fields that define a customer group and the numbering order, such as CustomerID, TransactionDate, and a unique TransactionID tie-breaker. Make sure the table or query has a unique row identifier before creating the occurrence-number expression.
Assign Occurrence Numbers with a Select Query and DCount
Use this when you need a dynamic, read-only occurrence number for each duplicate customer record without changing the underlying table.
The query must order records within each customer so that DCount can count earlier records. A unique TransactionID tie-breaker prevents rows on the same date from receiving the same occurrence number.
In Microsoft Access, open the database, click the Create tab, and select Query Design. Add the table that contains the duplicate customer values, then close the Show Table dialog.
Double-click CustomerID, TransactionDate, TransactionID, and any other fields you want to display to add them to the query grid. Set CustomerID to Ascending, TransactionDate to Ascending, and TransactionID to Ascending.
In the Field row of the next empty column, enter: OccurrenceNo: DCount("TransactionID","YourTableName","CustomerID=" & [CustomerID] & " AND (TransactionDate<#" & Format([TransactionDate],"mm/dd/yyyy") & "# OR (TransactionDate=#" & Format([TransactionDate],"mm/dd/yyyy") & "# AND TransactionID<=" & [TransactionID] & "))"). Replace YourTableName with the actual table name.
Click the Run button on the Design tab. Confirm that the first customer is numbered 1, 2, 3 and the second customer starts again at 1.

Store Occurrence Numbers with an Update Query
Use this when you need to persist the occurrence number in the table for matching, imports, or later processing.
Use WPS Office as a Free Microsoft Office Alternative for Exported Access Data and Spreadsheet Tasks
If Microsoft Access is not available or you only need to work with exported data and Office files, WPS Office offers a free, lightweight alternative with a familiar ribbon interface. It opens Microsoft Excel, Word, and PowerPoint files with high compatibility, so you can continue cleaning, analyzing, and reporting outside Access.

Frequently Asked Questions
Can an AutoNumber field assign separate sequential numbers for each customer?
No. An AutoNumber field creates a global unique row identifier across the whole table. It does not restart numbering for each customer group.
Why do I need a unique tie-breaker field like TransactionID?
The tie-breaker ensures deterministic order when multiple records have the same customer and transaction date. Without it, DCount may assign the same occurrence number to tied rows.
Can I store the occurrence number in the table instead of using a calculated query?
Yes. Add a numeric field to the table, create an Update Query, and place the DCount expression in the Update To row for that field.
Does WPS Office include Microsoft Access?
No, WPS Office does not include a Microsoft Access database module. It provides Writer, Spreadsheets, Presentation, and PDF tools, and can work with exported Excel or CSV files.




