How do I renumber my table in Access? 1

[tbiceps]

I want to renumber my auto number key in my table. I have deleted numerous records, and renumbering my table records would make life less complicated.

 

[Lilliabeth]

There is no good reason to do that. Autonumbers are supposed to be meaningless. If you start caring what they are, you'll be chasing that rabbit forever.

http://www.mvps.org/access/tencommandments.htm

 

[tbiceps]

I don't care, however the customer does care. To the customer, this request is not meaningless. Is there a way to facilitate this request?

 

[Lilliabeth]

First of all, I never said the request was meaningless.

I said autonumbers are supposed to be meaningless. I imagine you would have a hard time finding anyone here to disagree with that fact.

Second, if you would have searched, you'd have found these threads:

http://www.tek-tips.com/viewthread.cfm?qid=883813

http://www.tek-tips.com/viewthread.cfm?qid=828165

Third, you might choose to do the following, but, again, I don't recommend it because you will be chasing that rabbit forever:

This will change all of the numbers, not fill just fill in gaps.

Change the Start Value of an Autonumber Field
1. Create your table with an AutoNumber type field, but don't enter any records.
2. Then create another table with only a single Long Integer Number type field. This field must have same name as the AutoNumber field in the first table.
3. Enter one record in the second table that is a number one less than the required start of the AutoNumber for the first table.
4. Now create an append query to append the record in the second table to the first table and run the query.
5. You can now delete the second table and begin entering your data into the first table.

 

[Lilliabeth]

I should have reminded you:

Keep in mind if you follow the steps, you will lose the data necessary for relationships to child tables.

There are instructions in the FAQs as how to create a incrementing number without using autonumbering. Perhaps that would suit your customer better.

 

[tbiceps]

Thank you!