Microsoft Access Tip: Creating an AutoNumber Field Starting with a Number Greater than 1

In Microsoft Access tables, the AutoNumber field type allows you to assign a unique sequential number to each row in a table. AutoNumber fields are often used as the Primary Key, since they are unique and permanent (i.e. the value cannot be changed once it is assigned).

For new tables, AutoNumber begins with 1, and is incremented by 1 with each new row. However, we occasionally want to start the AutoNumber at a number higher than 1.

To do this, create an Append query to insert the value of the ID one less than your desired starting number (or Seed value). For instance, assume that you want to start with the Seed value 1000.

First, run this query:

INSERT INTO tblName ( ID )
SELECT 999 AS Expr1

where tblName is the table name, and ID is the name of the AutoNumber field.

Then, run a second query to delete the row that you inserted:

DELETE FROM tblName WHERE ID=999

(Alternatively, you can simply open the table and manually delete the row that you inserted.)

Now, the next record added to the table is assigned the value 1000.

This method works with a new table, or with a table whose AutoNumber has not yet reached the Seed value. (Of course, the ID field for existing rows will not be changed, but subsequent rows will start with 1000.)