changing auto number values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Steve,
Thanks. Yes, I do have relationships based on that A/N field. How would i safely *cater* to that? TIA
 
Gary said:
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
Click to expand...

You should not need to reset an autonumber field.

Autonumber fields are not designed to be seen by the user. They are
designed to be used to provide a unique number for each record. Not a
consecutive number, just a unique number. This it does well.

Trying to make it keep numbers in order or consecutive, will drive you
crazy.

If you want numbers in order it is far better to use a integer filed and
make up your own method of incrementing the numbers based on your needs.

Trust me on this. Many many people have this problem and almost all end
up doing as suggested.

Used as intended and used for linking tables, autonumber is the better
tool, but you don't want users to see those numbers because they are going
to be bothered by the odd missing number.
 
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
 
Back
Top