Renumbering Auto-Increment numbers

[svcghost]

Hey guys,



I have a table with a primary key that is auto-increment. I deleted the first 8 records, which now makes the 9th record the first record in that table.

Is there an easy way to renumber these records so the previously-9th record now has a primary key value of 1. And the previously-10th record now has a primary key value of 2, and so on?

[weekend00]

If you need to control the number, you should not use autonumber.
autonumber doesn't guarantee the continuity.

[svcghost]

If you need to control the number, you should not use autonumber.
autonumber doesn't guarantee the continuity.

Alright. I'll manually change them to be a number system. I guess I just used auto-number because there were 80+ records and I didn't want to manually number them. Guess being lazy doesn't pay off!

So I'm assuming that means no, there is no way to reset the numbers.

P.S. Is there an easier way to change the data type for a field if it's part of a bunch of relationships, WITHOUT destroying those relationships?

[Access_Blaster]

svcghost,

See if this helps.

http://allenbrowne.com/ser-26.html
http://allenbrowne.com/ser-40.html

Richard

[NTC]

the posted advice is correct. autonumber is a critically important way of assurring a unique row ID is made automatically. but is not the vehicle for display - as you have found out.

a database table fundamentally has no order. that is an abstraction for humans. a row is a row. what you want to do is put the running order in your display (i.e. form or report) so that no matter when/what records get added/deleted - when you generate a display it will have row numbering. don't do it at the table level.

so investigate row number in reports/forms - I think this will meet your needs.

Hope this helps.

[LillMcGill]

The beauty of autonumber is that it is nonsensical... no one ever needs to change it, no one needs to ever see it. It is true that if you care what value autonumbers are, you aren't using them correctly.

But if you delete the records and then run compact and repair, that should reset them to 1.

[NTC]

just a note: Compact/Repair does not reset the autonumbers as previously suggested.

and it should not - because these values can be cross referencing foreign keys to other tables.

[LillMcGill]

NTC, I respectfully disagree.

In the original post he said he deleted all the records.

Compacting a database does indeed reset the autonumbers for EMPTY tables. Please give it a try.

As I said... "if you delete the records and then run compact and repair, that should reset them to 1 "

[NTC]

and I respectfully disagree with your respectful disagreement. His original post was:
"....And the previously-10th record now has a primary key value of 2, and so on? ..."

my interpretation is thus that the table isn't empty, and he is attempting to find sequential numbers for display purposes of which compact/repair is not the solution.

you are right that a totally empty table c/r can reset autonumber - but I don't think that is the topic here.......