MS Access – AutoNumber Field

We tend to see numerous questions relating to MS Access Autonumbers. Specifically, regarding autonumber not following sequence, skipping sequence.

Regardless of how one might interpret what is written in the help files, AutoNumbers should never, ever, ever, be relied upon as a sequential number. Nor should they ever be used/displayed to the end-user. The simple fact of the matter is that AutoNumbers are merely unique identifiers for each record. Even when set to be incremental, the AutoNumber can be indexed even though no record was actually inserted into the table. This is not a bug, this is simply the way Access was developed.

So what does one do if they require a sequencial number to attribute to each record. The solution is actually very simple. Create a new field in your table and then you can used an equation such as =Dmax(…)+1 to generate the next number in your sequence. But at the end of the day, if you want a sequential number that will not jump sequence and can be faithfully relied upon, you have to create it yourself!