Unanswered: Newbie question:how to reset a field with data type of auto number
For example I have a "Customers" table with two field:
customerID (data type: Auto number)
customer_name: (data type: text)
And I have about 50 records in this table, of course with each record there are in customerID fileld and autonumber (1,2,3,4....48,49,50).
For some reasons, I deleted all records in the table and reinputed customers names. But when reinputing, the autonumber not is 1,2,3,4...., the autonumber is 51,52,53,54....
How can I reset the autonumber in customerID field so that the autonumber will
After you've added any data into the table, Access won't let you reset or create an autonumber field. The instructions in Microsoft Access Help only work if you want to start renumbering at a higher number. I had fun with this just last week. What you need to do is:
1. Copy the table's structure only (an option when you try to paste the copy).
2. Create a query of your old table that lists all the fields sorted in the order you want.
3. Change the query to an Append query. Enter your new table's name. Since the field names are all the same, it'll automatically pick the fields you want to append to. However, make sure the field with the Autonumber is left BLANK- you don't want to copy these numbers! As the records are appended, the new table with start Autonumber at 1. Check the number of
records in each table to make sure all the records copied over.
4. Delete your original table, or if you prefer, rename it something like tblTableName-OLD. Delete any relationships if you have to. Rename your new table (the one with the correct autonumbering) to the original table's original name. Re-establish any table relationships.
By the way, why do you want to renumber the records? There's an old post on the boards somewhere about Autonumbering, and how it should never be used for users to "see", because users always assume the number means something, which you don't want with Autonumber fields. There will eventually be skipped numbers. If a user enters half a record and then cancels, Access will not reuse that number. I did it last week only because I was appending from different tables, so I had records 1-9, then the numbers jumped to 1000. The number can be a handy reference, but if the user thinks that number is counting records and can't grasp that it's a reference number only, not a sequential number, then you might want to think about a new way for users to identify records. It's a mental thing, not technological.
Compacting the database will also 'reset' the autonumber. However it only puts the number back to the next highest number. For example if you have 150 records in a table from 1 to 150, and you delete the last 50 records, your autnumber will be at 151. But if you compact your database it will go back to 101. When I develop a database I will put in test data, then when I ready to give it to a client I delete all the records from the tables and compact them to get the Autonumbers back to 1.