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?

 

[Joseph Meehan]

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?

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[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.