How to Create a Year and Sequential Case Number in Microsoft Access
Question details
The user needs to generate and display a unique identifier combining a year and a sequential case number (e.g., 2021-1) in Microsoft Access.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Designing a database table or query that requires a structured, unique case identifier based on the current year and a sequential number.
- Observed behavior
- Combined identifiers are difficult to query and sort. The best practice is to store the year and sequence number as separate atomic fields and concatenate them only for display.
Ensure you have Design View access to your Microsoft Access database tables and queries before modifying the field structures.
Store Values Separately and Concatenate for Display
The best practice in relational databases is to store the year and sequence number as atomic values and combine them dynamically in queries or reports.
Relational database fields should contain atomic (indivisible) values. Storing the year and sequence number in separate fields ensures data integrity and makes sorting, filtering, and resetting the sequence much easier.
Open your table in Design View. Create two separate fields: name one 'TheYear' (set data type to Number) and another 'SeqNo' (set data type to Number).
Create a new query based on your table. In the Field row of the query design grid, enter the expression: ID: [TheYear] & "-" & [SeqNo].
Use the new query as the Record Source for your forms and reports. Bind the calculated 'ID' field to a text box for display purposes.
Generate Sequential Numbers Using VBA
Use Access VBA to automatically calculate the next available sequence number for a given year when a new record is created.
Looking for a Lightweight, Free Office Suite?
While Microsoft Access handles complex relational databases, WPS Office is the perfect lightweight alternative for managing your daily exported data, documents, and presentations without subscription fees.
- 1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install WPS Office: Run the downloaded file and follow the straightforward on-screen instructions to install the suite.
- 3. Analyze Exported Data: Open WPS Spreadsheet to import and seamlessly analyze data reports generated from your databases.
Frequently Asked Questions
Why shouldn't I store the combined year and sequence number in a single Access field?
Storing them together violates relational database principles (atomicity). It makes extracting just the year or sorting records numerically by the sequence number much more difficult. Storing them separately and combining them for display is safer and more efficient.
Can I use an AutoNumber field to generate sequential case numbers?
AutoNumber fields in Access are designed strictly to provide a unique identifier, not a gapless sequential number. If a user starts creating a record and then cancels, the AutoNumber will skip a value, breaking your sequence.
How do I reset the sequence number to 1 at the start of a new year?
You can implement a VBA script in the BeforeInsert event of your form. The script checks if any records exist for the current year using DCount or DMax; if none exist, it assigns '1' to the SeqNo field, effectively resetting the count for the new year.
