changing auto number values

G

Gary M Ruffin

This is probably easy - I have an auto number key field that i would like to reset to zero and then re-number the existing records, and then restart incrementing new records from there. Is this possible? If so, how? Any help is appreciated. Thanks
-- Gary
 
S

Steve Schapel

Gary

probably the easiest way to achieve this would be to simply delete the
existing AutoNumber field, and then just add a new AutoNumber field to
the table. This should immediately achieve your purpose as stated.
However, do you have other tables in the database which are related to
this table, using the value in the existing AutoNumber field as the
basis of the relationship? If so, you will need to cater to this in
your re-numbering.
 
G

Gary M Ruffin

Steve,
Thanks. Yes, I do have relationships based on that A/N field. How would i safely *cater* to that? TIA
 
S

Steve Schapel

Gary,

In that case you would need to do this in several steps:
1. Make sure you have a backup copy of your database
2. Add a new Autonumber field to the table
3. Add a new number field to the second table
4. Make a query based on the tables joined on the existing relationship
5. Make this an Update Query, and update the value in the new field in
the second table to the value of the new autonumber field in the first table
6. Delete the existing autonumber field from the first table and the
existing foreign key field from the second table
7. Rename the new fields in the tables as applicable
 
Top