help with autonumber

[CAM]

Hi, I have a table that already has about 55,000 records. I want to add an
autonumber in order for this table to have a uniquID field. However, I want
it to autonumber in a particular order. I thought that if I first sorted the
table by the field that I wanted it numbered in, saved it, and then added a
new field with type autonumber, that it would be in the 'correct' order. No
such luck. Can anyone help me with this? Thanks,
CAM

 

[Lynn Trapp]

No, it won't work that way AND, as soon as users start entering new records,
your records will be "out of order." If you can sort on a particular field
now, then you can sort on it after you add the autonumber field.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[CAM]

Hi Lynn,
The field I can sort on now will only work for current records, not for
future ones, so I had hoped that an autonumber field would capture the
current sorting from the already existant field and then add numbers as
records are added in the future to allow us to sort them in the order that
they are entered.

Can anyone else help me out?
Thanks,
cam

 

[John Vinson]

Hi Lynn,
The field I can sort on now will only work for current records, not for
future ones, so I had hoped that an autonumber field would capture the
current sorting from the already existant field and then add numbers as
records are added in the future to allow us to sort them in the order that
they are entered.

Can anyone else help me out?

Create the new table with the autonumber, empty; run an Append query
to migrate your data into it, sorted in the desired order.

Bear in mind that Autonumbers CANNOT be counted on to be gapless or
even in ascending order (if you Replicate your database they'll become
random).

John W. Vinson[MVP]

 

[Pat Hartman]

If your existing records actually have unique numeric values already, you
could use them as the new autonumber value. In your append query, map the
old unique numeric value to the new autonumber columns. The autonumber will
increment from the highest value appended. So, if you appended 55,000 rows
and the values ranged from 1-93,482; the next available autonumber value
would be 93,483.

An append query is the only place where you can supply a value to be used as
the autonumber. This is a very useful tool when converting data from old
applications. It allows you to keep the old key and continue on from that
point. It is even more useful, if the appended records had related
many-side tables because they could be input without modifying their foreign
keys.

 

[CAM]

Hi John,
This worked out great. Thanks. And, thanks also for the reminder about the
drawbacks of autonumber fields. For the particular purpose though, it will
work out fine.

Thanks,
Cheryl