Autonumber field order

[vickiwells]

I need to add an autonumber to existing data in a particular order in Access 2007. I've set the autonumber field to long integer, increment, no duplicates. Before adding the autonumber field my table is sorted in the order I need the autonumbers to be assigned, starting with 1. However, the autonumbers are assigned in a totally different order when I reopen the table. They start with 1, but not on the record I need it to. I've done "compact and repair" between creating the Autonumber field and actually opening the table, but no luck. Any ideas?

 

[winshent]

one solution is to create another table with the copied structure, then append to that table in the required order..

 

[boblarson]

I need to add an autonumber to existing data in a particular order in Access 2007. I've set the autonumber field to long integer, increment, no duplicates. Before adding the autonumber field my table is sorted in the order I need the autonumbers to be assigned, starting with 1. However, the autonumbers are assigned in a totally different order when I reopen the table. They start with 1, but not on the record I need it to. I've done "compact and repair" between creating the Autonumber field and actually opening the table, but no luck. Any ideas?

1. You are under the mistaken impression that the data within tables is stored in any particular order. Think of it as a big bucket that keeps getting fish piled on. It isn't necessarily going to stay in the same order as when you put it in. You pull out a fish from underneath many others and then some slide into place where they were not in that order before.

2. When you "sort" a table in the table you are not sorting the storage of that data. When you look at a table (open a table) you are actually opening a QUERY (a system one) that displays the table's contents. As with any query you can sort on various fields, but it doesn't change the order of the data within the actual table.

3. Adding an autonumber is one thing but adding an autonumber for use as you have described is not a good idea. Autonumbers are really only for guaranteeing you a UNIQUE number. They don't necessarily increment even when set to increment. Usage of an autonumber is really only for use as a surrogate key and then nobody should look at it or care what it is, as it is for the system's use.

4. For the numbering you seek you should use DMAX + 1. You can apply that as an Update query to the data sorted as you like.

 

[vickiwells]

Thanks, but I already did that, that's how I got the table without the autonumber. But I think I just figured out the solution. Instead of using a query to append to the empty table structure, I used copy, paste, append to existing table. The autonumber stayed in the right order. Thanks for the reply!