How to Create Seasonal Sequential Numbers in Microsoft Access
Question details
The user needs to generate consecutive sequence numbers that restart at 1 for each new season or shipment period without creating separate databases.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Tracking pallets, shipments, or records sequentially based on a specific season, ensuring the numbering resets at the start of each new period.
- Observed behavior
- The default AutoNumber field does not reliably restart at 1, cannot be easily reset per season, and is designed for unique internal IDs rather than unbroken sequences.
Before making changes to your database schema or running update queries, ensure you have backed up your Microsoft Access database file. Standardize your season names or shipment date formats to ensure the grouping logic works flawlessly.
Calculate a Custom Sequence Number Using DMax
The most reliable method to create consecutive numbers that reset per season is to use a separate numeric field and calculate the next value using the DMax function via VBA.
Relying on AutoNumber for continuous human-readable sequences is a common pitfall. AutoNumber guarantees uniqueness but does not guarantee continuous numbering (e.g., deleted or canceled records leave gaps). Creating a custom sequence field allows precise control.
Open your table in Design View and add a 'Season' or 'ShipmentDate' field (Short Text or Date/Time) to identify the tracking period.
In the same table, add a new field named 'SequenceNumber' and set its Data Type to 'Number'.
Open your data entry form in Design View, select the Form in the Property Sheet, and click on the 'Event' tab.
Click the ellipsis (...) next to 'Before Insert' and open the Code Builder. Write VBA code using the DMax function to find the highest sequence number for the current season, such as: Me.SequenceNumber = Nz(DMax("SequenceNumber", "YourTableName", "Season = '" & Me.Season & "'"), 0) + 1.
Save the form, switch to Form View, and enter a new record to verify the sequence increments correctly and restarts for different seasons.
Reset AutoNumber Using Compact and Repair
If you completely clear out your database, you can force the AutoNumber to restart at 1. However, this is heavily discouraged for ongoing seasonal tracking.
Manage Seasonal Data and Sequences Easily with WPS Office
While Microsoft Access requires VBA coding for custom sequences, WPS Spreadsheet offers a highly compatible, free, and lightweight alternative for tracking shipments, pallets, and seasonal data. You can easily manage sequential numbers using simple formulas without complex database programming.
- 1. Set up your tracking sheet: Open WPS Spreadsheet and create headers for 'Season', 'Item Name', and 'Sequence Number'.
- 2. Enter the sequence formula: In the Sequence Number column (e.g., C2), type the formula =COUNTIF(A$2:A2, A2) where 'A' is your Season column.
- 3. Apply to new records: Drag the fill handle down. The formula will automatically count occurrences per season, instantly generating a perfect restarting sequence.
Frequently Asked Questions
Can I use AutoNumber for invoice or pallet numbers?
It is highly discouraged. AutoNumber is designed to generate unique system IDs, not unbroken sequences. If an entry is cancelled or deleted, that AutoNumber is skipped permanently, leading to gaps in your pallet or invoice numbers.
Do I need to create a new Access database for each season?
No, creating a new database annually or seasonally is inefficient and makes historical reporting difficult. Instead, add a 'Season' or 'Year' field to your main table and calculate sequences grouped by that specific field.
How does the DMax function work for sequential numbers?
The DMax function looks up the highest existing numeric value in your sequence column for a specified grouping (like a season). By adding 1 to that maximum value through VBA or Macros, you generate the exact next sequential number for your new record.
Why did my AutoNumber jump to a random large number?
This can happen due to database corruption, replication tracking settings, or specific types of appended queries. It reinforces the rule that AutoNumber should only be used as a hidden primary key, never as a visible sequence.
