AutoNumber to re-number

[Allilue]

Hello,

I have an AutoNumber field that numbers Names that are in alphabetical order. If I add a name that will get slotted between records based on alphabet, the AutoNumber feature will assign a number following the last one. Is it possible to re-number again based on the new list?

 

[Guest_imported]

Hi Allilue,

I had a similar problem a week ago. After deleting and adding some new records in my table, the first AutoNumber value no longer started at 1 as I wanted. Perhaps there is a more elegant solution, but I ended up deleting the AutoNumber field and then re-adding it. You could make a copy of your table and give it a try. Good luck.

KK

 

[smedvid]

I simply created by own auto-number numeric field. Before a new record was added via Insert, I simply did a SQL - Select MAX(Field) to determine the next number + 1. If a re-order was needed, I wrote a simple Update routine that replaced values accordingly. htwh Steve Medvid
Atlas Commerce ("ebusiness evolved&quot

http://www.atlascommerce.com

 

[jagilman]

Allilue, just for kicks, how are you using the numbers where it would matter what the order is??

John A. Gilman
gms@uslink.net

 

[chiph]

Allilue -

The AutoNumber feature is designed to assign each record a unique identifier. And that's all.

If you want to sort your records, you'll have to include an "Order By" clause on the end of your SQL. Remember -- SQL is not guaranteed to bring the records back in any particular order unless you tell it to. MS-Access cheats and brings them back in inserted order (mostly).

To speed things up, you might want to add an index on columns you frequently use an "Order By" on.

In my last project, we used AutoNumber to generate a unique ID for each row, but didn't use it as the primary key, since nearly all of our requests used another column (Manufacturer Part Number).

Chip H.