Assign new autonumbers to sorted table possible?

[Jeweladdict]

I have 2 tables, one is ITEM, the other is UPC
ITEM includes name, price, size and an autonumber id field as the
primary key
UPC has 2 fields, a upc string as well as a foreign key to the primary
key of item

a sample mdb file can be found at http://jeweladdict.com/temp.mdb

When inserting new ITEMs into the ITEM table, all items are inserted at
the bottom with a new autonumber associated to it. I would like to sort
the ITEM table by name, and then reassign all autonumber primary keys
based on the new sorting. The twist is that the relationship to the UPC
table must also be updated with the new autonumber keys...


is this possible?

 

[Jeweladdict]

Since the mdb file is already being autonumbered.... is there anyway to
turn the autonumbering off? I tried switching the column format but
since it has dependents/relations it wouldnt let me =/

 

[Joan Wild]

There is no need to reassign all the autonumbers. Use a query for data
interaction; you can sort the Item table by name in the query - you really
shouldn't care what the sort order is in the table - in fact it has no sort
order.