Resorting Auto Numbering (1 Viewer)

[joepele]

*I know that this may not be the best way of resolving the problem but this is not the actual problem but my way to simplify of explaining the "problem".

I have a Table with 3 fields.
Field 1 - Auto Number
Field 2 - First Name
Field 3 - Last Name

Initially, the Table was sorted via First Name,
but now, there is a change in requirement to sort by Last Name.

Next requirement is to number (Auto Number) according to the Last Name.

Current Sorted via First Name
Auto Number - First Name - Last Name
1 - Ben - Young
2 - Chuck - Norris
3 - Dennis - Johnson
4 - Ernest - Anderson

If sorted via Last Name
4 - Ernest - Anderson
3 - Dennis - Johnson
2 - Chuck - Norris
1 - Ben - Young

Desired result
to Sorted via Last Name & (Auto Number) via sort.
Auto Number - First Name - Last Name
1 - Ernest - Anderson
2 - Dennis - Johnson
3 - Chuck - Norris
4 - Ben - Young

Is there a way to do renumber / resort the auto number this?



*I have thousands of records....

 

[gemma-the-husky]

you dont need to do anything at all

....

just use a query based on your table and sort the QUERY in the order you want - using a query is 99% of the time the way to go, rather than using the table directly

 

[Rabbie]

you dont need to do anything at all

....

just use a query based on your table and sort the QUERY in the order you want - using a query is 99% of the time the way to go, rather than using the table directly

That won't renumber the autonumber key. The only way to do that is to use an append query to copy your data sorted in the required order into a new table. Just copy the records without the autonumber keys and they will repopulate in the new table in the order you want.

 

[Mike Vytal]

Joepele, like Rabbie said, do an append query, using your original table as the source. Append the records to a new table without your 'autonumber field'. Remember to put the 'LastName' field to the left of the 'FirstName' field and sort both ascending. After you have appended the records, open your new table and add an ID (autonumber) field. You're done...

 

[gemma-the-husky]

what i meant was

why would anyone want or need to renumber an autonumber key

----
if they think they need to do this, they are probably misundestanding the problem

 

[joepele]

Actually, wouldn't that be similar to deleting the auto number field and creating that field again?

 

[boblarson]

what i meant was

why would anyone want or need to renumber an autonumber key

----
if they think they need to do this, they are probably misundestanding the problem

Or actually misunderstanding the purpose of Autonumbers. Autonumbers ONLY guarantee you a UNIQUE number. They are not meant to be used for meaning UNLESS you can live with the fact that:

1. They may get gaps due to deleted records or records that were started and then aborted.

2. They are not guaranteed to actually increment by one each time. They may actually jump several, if not several hundred (or thousand) numbers at any given time.

3. They could be negative numbers and random if you move to replication.

So, if you need a specific numbering sequence, then create it yourself.

 

[gemma-the-husky]

Actually, wouldn't that be similar to deleting the auto number field and creating that field again?

absolutely not, and of course not

eg if you have a customer with autonumber 123, and orders linked to that customer, the orders wiill have the foreign key 123 present in that record. (that is how it SHOULD work)

now if you try to renumber your autonumber references, then the autonumber of 123 may changes to 75 say and you will lose the link to the other table.

now if this customer is called "J Smith and Co", say, it DOESNT MATTER what the number is, because you will show the user the information "J Smith and Co". And if you need to change this name, you can, because the database is actually building the relationship based on the underlying autonumber ID.

Thats is why it doesnt matter what your autonumber IS - its sufficient to know that it is guaranteeed to be unique, - and you dont EVEN need to know what that number ACTUALLY IS. ever really, once you have debugged your app properly.

Use a combobox or other technique to search for a name, but use that name to derive the number. and behind the scenes only, programatically manipulate things based on the number.