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
For new tables, AutoNumbering 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
(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