Why does autonumber started at 2 with 1 around the middle and 4 in the last? (1 Viewer)

Mackbear

Registered User.
Local time
Today, 07:57
Joined
Apr 2, 2019
Messages
168
Hi, Good day! Reaching out again for help....

I did an autonumber field and why does autonumber started at 2 with 1 around the middle and 4 in the last? How do a fix it so that it will be in order?
 

June7

AWF VIP
Local time
Today, 04:57
Joined
Mar 9, 2014
Messages
5,423
Don't know but really shouldn't matter. All autonumber is supposed to do is serve as a unique record identifier. Order and gaps should not be a concern.

If you want to provide db for analysis, follow instructions at bottom of my post.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:57
Joined
Oct 29, 2018
Messages
21,357
Hi Mack. Did you designate the Autonumber field as a Primary Key?

PS. If this thread is related to this one, then the reason would be corruption.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:57
Joined
Sep 21, 2011
Messages
14,041
What is the sort order for the table?

Hi, Good day! Reaching out again for help....

I did an autonumber field and why does autonumber started at 2 with 1 around the middle and 4 in the last? How do a fix it so that it will be in order?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:57
Joined
Feb 19, 2002
Messages
42,970
In a relational database tables and queries are UNORDERED sets. That means that the database engine might return the results as 1,2, 3 one time and 3,1,2 the next. The ONLY way you can ensure that a recordset is returned in a specific, consistent order is to use a query with an Order By clause.

Access tends to fool people because normally when you open a table in ds view, it "appears" to be sorted by PK order but believe me when I tell you that, you cannot rely on this. The appearance is exacerbated if you have saved a sort order for either the table or a query. When you compact a database, one of the tasks performed is to sort ALL tables into PK order and write them back to disk in that sequence. As long as you don't update any of the early records, they will appear to be in PK order. However, if you update record #1 and make it too large to fit back in its original physical position on the disk, Access will add the changed record to the first available free space, which in the case of Access will be at the end of the physical file. More sophisticated database engines spread freespace throughout the file space so records will generally be written back some place closer to where they were before they were updated.

Anyway, you can prove all of this to yourself if you are so inclined. You just need a large enough set of data and you need to make a sufficient field size increase to force Access to move the record to see the effect. The reason you need a large number of records is because data I/O is done at the physical record level. This is dictated by the device. For example, on one disk, the size of a physical record might be 2K but on another it might be 4k and if your average record size is 100 bytes, there will be multiple logical records in one physical record. Access (like most other relational databases) will always rewrite the entire physical record when one logical record is updated, so you actually need to force the physical record size to be exceeded for Access to put the updated record back at the end of the file space and into the last physical record of the table.

That is a very high level explanation of how records are read and written in a database.
 
Last edited:

Mark_

Longboard on the internet
Local time
Today, 05:57
Joined
Sep 12, 2017
Messages
2,111
To add to the others, when you delete records, new records are written over their position. Autonumber also does NOT change your numbers when you delete a record, so you can start with

1 Apple
2 Banana
3 Cranberry

You delete Banana, add Date, then add Banana back AFTER, you'll get
1 Apple
3 Cranberry
4 Date
5 Banana

Autonumber "2" won't be reused. If you view then in "Alpha order" for the foods, you'd have
1 Apple
5 Banana
3 Cranberry
4 Date
 

Mackbear

Registered User.
Local time
Today, 07:57
Joined
Apr 2, 2019
Messages
168
In a relational database tables and queries are UNORDERED sets. That means that the database engine might return the results as 1,2, 3 one time and 3,1,2 the next. The ONLY way you can ensure that a recordset is returned in a specific, consistent order is to use a query with an Order By clause.

Access tends to fool people because normally when you open a table in ds view, it "appears" to be sorted by PK order but believe me when I tell you that, you cannot rely on this. The appearance is exacerbated if you have saved a sort order for either the table or a query. When you compact a database, one of the tasks performed is to sort ALL tables into PK order and write them back to disk in that sequence. As long as you don't update any of the early records, they will appear to be in PK order. However, if you update record #1 and make it too large to fit back in its original physical position on the disk, Access will add the changed record to the first available free space, which in the case of Access will be at the end of the physical file. More sophisticated database engines spread freespace throughout the file space so records will generally be written back some place closer to where they were before they were updated.

Anyway, you can prove all of this to yourself if you are so inclined. You just need a large enough set of data and you need to make a sufficient field size increase to force Access to move the record to see the effect. The reason you need a large number of records is because data I/O is done at the physical record level. This is dictated by the device. For example, on one disk, the size of a physical record might be 2K but on another it might be 4k and if your average record size is 100 bytes, there will be multiple logical records in one physical record. Access (like most other relational databases) will always rewrite the entire physical record when one logical record is updated, so you actually need to force the physical record size to be exceeded for Access to put the updated record back at the end of the file space and into the last physical record of the table.

That is a very high level explanation of how records are read and written in a database.

Yes I did noticed that the order changes everytime i open the table. Thanks for this
 

Mackbear

Registered User.
Local time
Today, 07:57
Joined
Apr 2, 2019
Messages
168
Hi Mack. Did you designate the Autonumber field as a Primary Key?

PS. If this thread is related to this one, then the reason would be corruption.

Yes it is related to that post, i actually asked this because i was trying to figure out a solution where i need the numbers in order. Yes the autonumber is pk. I actually figured out a way to fix it and I posted it on the other post. Thank you!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:57
Joined
Oct 29, 2018
Messages
21,357
Yes it is related to that post, i actually asked this because i was trying to figure out a solution where i need the numbers in order. Yes the autonumber is pk. I actually figured out a way to fix it and I posted it on the other post. Thank you!

Hi. Yes, I saw it. Glad to hear you got it fixed.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:57
Joined
Sep 12, 2006
Messages
15,613
it's amazing how quickly access manages to stay on top of complex databases with multiple indexes.

Thanks for the explanation, Pat.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:57
Joined
Feb 19, 2002
Messages
42,970
You're welcome Dave. File I/O is obviously more complicated than my explanation but the explanation is detailed enough to convey the concept.
 

Micron

AWF VIP
Local time
Today, 08:57
Joined
Oct 20, 2018
Messages
3,476
It's all great information that I agree with, but I'm not sure your question was specifically answered IF you did what I think you did - which is add the autonumber field after the table had records. In that case these numbers will more than likely appear in a random order. Pretty much proves what Pat is saying IMHO. The only way you can force them to be in autonumber order in a datasheet view is to sort on that field and save that sort as a table property. However, I'm NOT saying you can rely on this order when using those records in any fashion. In fact, I'm in the midst of fixing a problem for someone else whose code makes use of the Last function and the results are proof of what's being said here.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:57
Joined
Feb 19, 2002
Messages
42,970
Last() retrieves the last physical record of the set. As you can imagine, that will be inconsistent since the set isn't sorted. Usually when people say "last", they mean the "most recent" so sorting descending by date will usually get the target record.

It is easy to be fooled while testing because no one else is manipulating data. Once an app gets out in the wild, you get invalid results and it may be days, weeks, months before anyone notices.
 

Micron

AWF VIP
Local time
Today, 08:57
Joined
Oct 20, 2018
Messages
3,476
sorting descending by date will usually get the target record
Only if you store date and time. If the field doesn't contain a specific time, it defaults to 12:00:00 AM thus all records for today would be 10/28/2019 12:00:00 AM. Can't sort on that.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:57
Joined
Feb 28, 2001
Messages
26,996
True that, Micron. Depends on whether the field was defined by Now() or Date(), however it was loaded.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:57
Joined
Feb 19, 2002
Messages
42,970
If you have an autonumber that assigns numbers sequentially, that can also be used for sequencing purposes. I have a CreateDT field on all my tables that defaults to Now(). It doesn't have anything to do with the action that created the row but it is a reliable indicator of the sequence in which rows were added. If you need to have some "last" logic based on business rules, then autonumber will not work and you will need to include date and time in the field that controls sequence, AND it will need to be unique so good luck with that.

When transactions are received by some batch means such as email or snail mail and added to the database later, they may not be added in date logical order. So the bottom line is - What does LAST actually mean in any particular situation?
 

Micron

AWF VIP
Local time
Today, 08:57
Joined
Oct 20, 2018
Messages
3,476
If you need to have some "last" logic based on business rules, then autonumber will not work
Tend to agree with that, but Max will work AFAIC. If you're saying you cannot get a unique sequential value from Now() then I disagree. Just convert it to a double.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:57
Joined
Feb 19, 2002
Messages
42,970
Dates ARE stored as doubles. No conversion is necessary. It would be unlikely to get a duplicate but I'm guessing that it is possible if two users hit the save button at the same time.
 

Micron

AWF VIP
Local time
Today, 08:57
Joined
Oct 20, 2018
Messages
3,476
Dates ARE stored as doubles. No conversion is necessary. It would be unlikely to get a duplicate but I'm guessing that it is possible if two users hit the save button at the same time.
You are correct of course. What I meant was format it as a double so that 2 dates don't appear to be the same value.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:57
Joined
Feb 28, 2001
Messages
26,996
Unfortunately, two dates CAN and WILL appear as the same value if you use NOW(), because it computes based only to the second. This is a side-effect of using the Windows internal time conversion routine. I had several duplicated log entries based on NOW() and had to add an autonumber to keep my event logs truly sequential.

The overlaps occur because the NOW() function computes your time to seconds and then stops computing. This is because the internal time base that NOW() uses is a crystal-controlled binary up-counter in units of JIFFIES (100 nsec). The function uses 64-bit math to repeatedly divide out days and computes the date based on some complex math. When it gets to the day fraction it remainders three more times - by 24, 60, and 60 - to convert the remaining counter to a fraction of a day. Though the fractions of a second COULD be derived from the up-counter because it has that much precision, NOW() stops at seconds.

In a shared database, it is unlikely but NOT impossible to get events posted at the same time to the second. When I decided I needed a bit more than seconds for some debugging, I changed the logging routine by using the TIMER() function, which gives you milliseconds since midnight. So I converted the function to store NOW() as a DOUBLE and added an INTEGER field that was TIMER() MOD 1000, from which I got the milliseconds. For "ordinary" logging, I just displayed the date field normally. When I needed the precise timing of events for debugging, I knew them to the millisecond through a specially formatted conversion routine that concatenated a formatted 3-digit fraction and decimal point to the short-time formatted date entry.

I looked into the high-precision timer, which is how I learned about the Windows internal clock. I quickly realized that using the high-precision stuff was going to be serious overkill so lived with the very rare timing overlaps that even a millisecond timer wouldn't totally stop. But the duplicate time entries occurred a thousand times less often.
 

Users who are viewing this thread

Top Bottom