Record Order in Access

[dloskot]

This is going to be a little hard to explain but here goes.
1. I created an access table with a record ID as a primary key and as AutoNumber. So as each record is added it gets the next Record Number.
2. The records also have a date. However the records were not added in date order. So if I sort by Record ID the dates are not in order and if I sort by Date Record ID is not in order.
3. I deleted the Record ID field sorted by dates and then added the Record ID (AutoNumber) back in thinking that the Record ID and Date would both be in order.
That is not what happened the Record ID went back to the order the records were added to the DataBase.

Is there a way to sort by date and then add Record ID that would result in the having the Record ID and date in the same order. There appears to be some internal "Record ID" that I can't see that is assigned as records are added.
I had thought of exporting the records sorting by date and importing the records back into the data base in data order and then adding Record ID field (AutoNumber) but I was hopping there is a simpler solution.

Hopefully someone can provide a solution.

 

[alansidman]

Is there any reason you need the record ID in a particular order. It is typically used in a DB to create a unique ID and serves no other purpose.

 

[dloskot]

No not really I just wanted to know if there was a way to do this. It is not critical but it there is a way it might also give me some insight for other things.

 

[alansidman]

In Access, the order of how data is presented in a table is of no consequence. To think there is, is a spreadsheet mindset. All data in a table is organized for presentation, not in a table, but in queries and if needed in reports.

 

[Joe4]

A good metaphor I once heard regarding data records in Access tables is to think of it as a "bag or marbles" - order really has no meaning.

If you did have reason to sort it in order that the records were received, then it would probably make sense to add a "Date Stamp" field to your table, that record when records are added to the table.
Or, you could record a "Record ID" field also. A simple way to do it would be to set up this field as a Long Integer, and every time a record is added to the table, have VBA code look up the maximum Record ID field value currently in the table, and add one to it.

 

[Micron]

Not clear to me if the date of data entry (not necessarily the date value in any field) is the critical order or not. If so, one needs a date/time stamp in a field and you can sort on that. I have read that the most reliable means of applying a sort is to use a query, although I cannot recall ever seeing an issue when a sort was applied directly in a table field.

To reiterate what was said here, autonumber fields simply guarantee (almost 100% reliably) that each record in that table has a unique identifier. That is its sole purpose and values in that field should not be used as meaningful data. I say in "that field" because the AN values are used in related tables as foreign key values so that is a distinction that probably should be made.

General: Use Autonumbers properly

 

[jackd]

dloskot,
Records are physically placed in the table in a position determined by Access. Autonumbers sole purpose is to uniquely identify records within a table. Autonumbers are not necessarily assigned in sequential order---often, they are, but it has been noted repeatedly that they are assured to be unique ONLY.
If your need is to select records from a table in sequential order, then use a Query with an ORDER BY clause.

From older UtterAccess wiki -Autonumbers
What they are NOT:
1. Row (record) sequence numbers.
2. An "order of entry into the table" number
3. A "gapless" series of numbers.
4. Editable numbers.
5. A series of (necessarily) always increasing numbers.
6. Intended to be viewed/used by end users of the application.
7. Predictable (as to what the previous or next one in the table is/or will be).
8. Reassigned, once deleted or discarded
9. A predictor/indicator of the number of rows in a table.
10. Intended to be used to "rank" or "sort" or "number" rows returned from the table.
11. Necessarily used to determine the default order the rows may be returned from the table.
12. Indicative of or related to any TimeStamp field that may also be in the table row.

What they are:
1. Unique numbers used to identify individual rows in a table.
2. Automatically created by Access when a new row is "instanced" by Access.
3. Great/Outstanding/Essential for use as the Primary Key of a table.
4. Great/Outstanding/Essential for use as "link points" by Foreign Keys in other tables.
5. Unchanging, once assigned to a particular table row