Add new autonumber column to table

[Astello]

How do I add a new column to an existing table, and fill it with number (starting at 1) so I can use that column as the PK? Step-by-step instructions would be great, I'm having a lot of trouble with this!

 

[gemma-the-husky]

just insert the column, and define it as autonumber - it will automatically populate itself. however to use it, you will then need to look at pretty well all your queries/forms/reports etc, and see if you need to amend these to use the autonumber.

if your stuff is already working ok you may not need to change everything immediately. its just generally that a single numeric key is more efficient to use than eg segmented text keys. You will still probably need to keep the same (at least some of them) secondary keys, as an autonumber key will not by itself guarantee the correctness of your data.

 

[Astello]

How do I define the column as autonumber?

 

[gemma-the-husky]

open the table in design mode, add a new field at the end, and select autonumber as the type.

 

[boblarson]

Just a quick note on this -

Autonumber will guarantee you an UNIQUE number as a key, but it WILL NOT guarantee that the number will stay in numerical order and if you delete a record, or a user starts to add a record and cancels, that number is gone forever so you will have gaps. It's best to not assign a meaning, other than a key, to an autonumber unless you don't care if there are gaps or that the number may not be consecutive (one example of when it may not be consecutive is if you use replication).