How to rearrange the autonumber based on a field?

[hshaker]

Hi there,

I have an autonumber field that obviously is unique for each record. My records also have a date field. I would like to rearrange the AUTONUMBER field so that it correspond to my records that are in chronological order.

I tried to delete autonumber field and reinsterting it in the table. Even that, does not put the records in the order that they were actually entered in database.

Thank you for your help.

 

[barrylowe]

Try this:

Open up your table in datasheet view.

Highlight the date field and order the table on that field using the Sort Ascending button on your toolbar.

Close the table and save the changes when prompted to do so.

Open the table in design view and add in a second Autonumber column.

Save the new design and then open the table in datasheet view. Have a look and make sure that the new column has the correct numbering.

Go back into design view and delete your original autonumber column.

While still in design view of the table click the properties button to open the properties window. Delete whatever appears in the Order By filed.

Save your cahnges.

 

[straybullet]

As Access only allows a single autonumber field per table, you would need to adjust the above slightly, removing the autonumber field before adding the new one. If this autonumber is used as a foreign key in another table, there will be problems with mis-linked records.
Bear in mind that autonumbers shouldn't be used for anything other than as a unique identifier. ex: there is always the possibility of needing to "backfill" an entry. That entry will never sort properly if the autonumber is the sort field.
I suggest using the existing date field for chronological sorting purposes in your forms and reports.
Use of Autonumber


Let them hate - so long as they fear... Lucius Accius

 

[hshaker]

Hi there,

My autonumber is not used as a foreign key in another table. I followed your suggestion and it does not work. It appears that autonumber is assigned randomly and not based on an order.

Any suggestions?

Thanks.

 

[JoeAtWork]

autonumber is assigned randomly and not based on an order

Kind of. It does increment, but you can get "holes" in the series if a record is deleted, or you start creating a record and then cancel.

There's nothing wrong with that as autonumbers were never meant to be used to keep a congruous set of numbers. Their only purpose is to create a unique number.

If you want a number that always increments in a series, you will need to write your own code to do that. There is a FAQ somewhere on Tek-Tips that shows you how to do that.

 

[MajP]

What is the purpose of this field? That will determine how to do it. If this is your primary key then this is a bad idea to change a primary key only to have it a continous set. If it is not a primary key, and you just want a continous, ordered list showing the order of the records entered, you can just write a query to number the list (since each has a date time) and not even save the value. EX.

SELECT Count(B.dtmEntered) AS CountOfdtmEntered, A.Name, A.dtmEntered
FROM tblA AS A, tblA AS B
WHERE (((A.dtmEntered)>=[B].[dtmentered]))
GROUP BY A.Name, A.dtmEntered
ORDER BY A.dtmEntered;