How to Create a Sequential Client ID by Site Code in Access
Question details
The user needs to create formatted client IDs (such as OCC099) by combining a specific site code with an auto-incrementing sequence number, without breaking database normalization principles.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Designing a relational database table where records require a unique, visually concatenated identifier based on a specific location or site code.
- Observed behavior
- The user is looking for a reliable way to generate and display combined string IDs securely, as combining two distinct pieces of data into a single permanent field violates normalization rules and risks duplication.
Before altering your table structures, verify that no other tables are actively relying on the old client ID format as a primary key. Always back up your Access database before applying structural changes.
Store Fields Separately and Concatenate for Display
The safest and most normalized approach is storing the site code and sequence number in two separate fields, combining them dynamically only when viewing the data.
Storing two discrete pieces of information in a single field violates normalization rules and complicates future data sorting or filtering. By using a display expression in your forms, queries, or reports, you maintain data integrity while achieving the desired visual format.
Open your table in Design View. Ensure you have two distinct fields: 'SiteCode' (Short Text) and 'SequenceNumber' (Number).
To prevent duplicate IDs, open the Indexes window in Design View and create a unique composite index using both the 'SiteCode' and 'SequenceNumber' fields.
When creating a Query, Form, or Report, add a calculated field or an unbound text box to display the combined ID.
Set the Control Source or query expression to: =[SiteCode] & Format([SequenceNumber], "000"). This will seamlessly output a combined string like OCC099.
Generate the Sequence Number via VBA Data-Entry Logic
If you require a physical per-site sequence number to be saved to the table, use VBA on your data-entry form rather than relying on DCount alone.
Looking for a Lightweight Alternative for Data Tracking?
While Microsoft Access is a specialized database application, many complex tracking tasks and sequential ID generation can be efficiently managed using spreadsheets. WPS Office offers a free, lightweight, and highly compatible alternative for your daily office tasks.
- 1. Download and Install: Download WPS Office for free and install it on your device to access Writer, Spreadsheet, and Presentation tools.
- 2. Open WPS Spreadsheet: Create a new spreadsheet and set up separate columns for 'Site Code' and 'Sequence'.
- 3. Combine IDs effortlessly: Use the formula =A2 & TEXT(B2, "000") to instantly generate your concatenated sequential client IDs without writing complex database code.
Frequently Asked Questions
Why shouldn't I store the combined client ID in a single table field in Access?
Storing multiple pieces of distinct information (like a site code and a sequence number) in one field violates database normalization rules. It severely complicates future queries, makes it difficult to sort numerically by sequence alone, and increases the risk of inconsistent data entry.
What is the problem with using DCount to generate sequence numbers?
DCount simply counts the total number of records that match a criteria. If you have 10 records and delete record number 5, DCount will return 9. The next generated sequence would be 10, creating a duplicate error because ID 10 already exists. DMax is generally preferred for finding the highest existing value.
Can I use an AutoNumber field to manage per-site sequences?
No. An AutoNumber field in Microsoft Access generates a single, continuous unique number for the entire table. It cannot be configured to restart at 1 or maintain separate counting sequences for different site codes within the same table.
