Change order of numbers assigned by AutoNumber.

[Guest]

I have a list of 800 items. Numbers were assigned by AutoNumber. Since
creating the list there is a need to reorder the list and change numbers
accordingly. I removed the Number field and reordered the list. When another
Number field is added and formated as an AutoNumber field, the list reverts
back to the old order. How can this be overcome?

 

[Douglas J Steele]

It can't. If the value of the Autonumber field matters to you, you probably
shouldn't be using an Autonumber. They're designed for one purpose only: to
provide a (practically guaranteed) unique value that can be used as a
Primary Key. There is no guarantee that there won't be gaps in the
numbering.

 

[Guest]

It is possible to do this as follows:-

Remove the autonumber field.

In design add a new autonumber column (just to give each record a unique
key), and make the primarykey a combination of your new fields to sort by and
the new autonumber (which must be the last part of the key).

Save the table.

Now if you compact the database, the records will get sorted by the primary
key (which is your new field).

Next delete the old autonumber, and add a new autonumber (which you can now
make the primary key).

A bit long winded but this will work.

 

[dd]

create a different table, then insert the data in the order you want.

 

[BruceM]

If you need the records to be in a particular order you should create a
query, which can be sorted. What is the list's purpose? Do fields in this
table participate in any relationships?

 

[Joseph Meehan]

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.

Start thinking of a table as nothing more than a bucket of data with no
order. Order needs to be defined. Autonumber is not a good or reliable way
of providing an order.