Turning sequential numbers into an Auto Number Field

L

LauraK

We converted an existing list in a Word Table into an Access Table. The
conversion process went well but now we would like the current Order Number
field to be our Primary Key and we want it as an AutoNumber field. We want
the sequential order number entered when we create a new record. When I try
to change the field type to AutoNumber, I receive an error that says you
cannot convert the current field to AutoNumber. Since Autonumber won't work,
is there another way to make this work?
 
K

Ken Snell [MVP]

Create a new table that is a duplicate structure of the current one, but add
a new field to the table, and make it the autonumber field.

Then write an append query that will copy the data from the original table
to the new table, except use the sequential number field as the source for
the autonumber field.

That will populate the autonumber field with the current values.

You then can do the necessary steps to either make the new table replace the
old table, or to delete data from the old table and add the autonumber field
and then append the data back to it from the new table, or many other
possibilities (depending upon your database design).
 
Top