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

[Mackbear]

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]

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]

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]

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?

 

[Mark_]

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]

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]

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]

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]

it's amazing how quickly access manages to stay on top of complex databases with multiple indexes.

Thanks for the explanation, Pat.

 

[Deleted member 147267]

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.

 

[Deleted member 147267]

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]

True that, Micron. Depends on whether the field was defined by Now() or Date(), however it was loaded.

 

[Deleted member 147267]

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.

 

[Deleted member 147267]

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]

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.

 

[Deleted member 147267]

If you look at a Now value that is displayed as a double, the time component is expressed in milliseconds AFAK. If you enter the two prompts (?) and position the cursor one line below the second and at the end, you can execute these within a second or less. The values are quite distinct and more precise than just one second.

?cdbl(now)
43772.4315972222
?cdbl(now)
43772.4316087963

 

[The_Doc_Man]

Micron, those ARE two different times as shown by experiment from my "Immediate Window."

debug.Print formatdatetime( 43772.4315972222 , vbGeneralDate)
11/3/2019 10:21:30 AM
debug.Print formatdatetime( 43772.4316087963 , vbGeneralDate)
11/3/2019 10:21:31 AM

You might indeed be getting 15 digits out of the DOUBLE variable, and it is ABSOLUTELY true that there is enough precision in there to give you milliseconds - in theory. But the pesky little conversion routines are the problem. If you expressed times to the fraction of a second, the conversion routines would deny that you had expressed a valid number. If you diddle with the fractions and convert them to dates, they will take you to the closest second, which is 1/87400.

If I recall this correctly, a DOUBLE gives you 53 bits. As we can see from the numbers, we are in the range where 16 bits are adequate to express the day number, and will continue for the next 22,000 days or so. If we look at 1/87400, that requires 17 bits (minimum) because 87400 (seconds per day) requires 17 bits. So far we have accounted for 33 of the 53 bits. The remaining 20 bits are where the fractions get interesting. To express 1000, you need 10 bits, so you still have room for milliseconds. BUT you only have 10 bits left for fractions, and you would have to start using the high-precision timer and a totally different way of storing the result if you needed faster than millisecond timing. That fraction actually CAN store microseconds but that is pushing it.

Before anyone asks, the old Digital Equipment / COMPAQ / HP OpenVMS system used the same concepts and had the same issues. How they are the same is a long story for a different venue.

 

[Deleted member 147267]

So if you're saying using the system clock in some sort of a routine isn't practical, I'd agree. All I'm saying is that in the course of normal use, the chances of 2 or more date/time values being duplicates down to the precision of double data type values is pretty much nil. This started with the supposition that going by Date alone was enough (IIRC) and I'm only saying it's not. One needs more precision.

As usual, your insights reflect a whole lot of experience in realms I've never dealt with, and are educational. Thanks for that!

 

[isladogs]

Both of you may perhaps be interested in my tests of 6 different methods of measuring time http://www.mendipdatasystems.co.uk/timer-comparison-tests/4594552971.

These include the Timer function, several different uses of the system timer such as GetSystemTime & GetTickCount as well as the High Resolution Timer.
Most of these do indeed measure to millisecond precision and the HRT to less than 1 microsecond. However its worth stressing that greater precision does not necessarily equate to greater accuracy.

 

[CJ_London]

87400 (seconds per day)

just a small correction - there are 86400 seconds in a day - plus 59 if you want to include leap seconds