Auto-Number fields in Access
Printer Friendly Page
Contents:
Gaps in Auto-Numbers
Resequencing Auto-Numbers
Special Formatting for Auto-Numbers
Gaps in Auto-Numbers
If you define a field as type
Auto Number, Access will assign a unique,
sequential number to a record when it is created.
These numbers should not be relied on for anything other than a unique identifier.
If record is deleted, that number will be missing. Auto numbering will not go back and
assign missing numbers. Also, if you start to create a new record and then cancel, the auto-number may be used up.
It will be missing from the set of numbers.
Gaps in auto-numbers are very common. There isn't any Access feature that allows
you to fill-in these missing numbers.
Resequencing Auto-Numbers
However, it is possible to
restart auto-numbers from 1 again:
- Remove all records from the table (Copy them if you don't want to loose them).
- Compact the database [Tools --> Database Utilities --> Compact and Repair database].
- Use an APPEND query to add your original records into this newly compacted table. (Don't append the original auto numbers)
Special Formatting for Auto-Numbers
What if you want auto-numbers that start with a prefix, such as IT-100 or PT-250?
In the database design, use the format property of the auto number field.
For the examples mentioned here you would enter "IT-"# or "PT-"# in the format property field.