Random AutoNumbers
By Richard Rost
4 years ago
Why Use Random AutoNumbers in Microsoft Access
In this video, I'm going to explain why AutoNumbers have an option to use random numbers instead of incremental ones.
Paxton in Modesto, California (a Gold Member) asks: Can you please explain why there's a setting for AutoNumbers to be random? Isn't the whole point that you want them sequential? Why would I want my OrderIDs all over the place like that?
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Links
Recommended Course
 Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, why would you want random autonumbers, unique
Intro In this video, we will talk about the reasons for using random AutoNumbers in Microsoft Access. I will explain what random AutoNumbers are, why you might want to use them instead of sequential ones, and how they help when splitting and later merging databases without conflicts. We will also look at how to switch your table to random AutoNumbers, what happens when you do, and common scenarios where random values are preferred over sequential IDs.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to explain to you why you would want to use random auto numbers. I got an email this morning from one of my Gold members, Paxton, in Modesto, California. Paxton says, can you please explain why there is a setting for auto numbers to be random? Isn't the whole point that you want them sequential? Why would I want my order IDs all over the place like that?
Well, Paxton, first I want you to watch this video. Auto numbers are not for you. You should not care what your auto numbers are. They could be sequential, there could be some missing, they could be random. That number is for Access to make sure each record is unique in your database and it uses it for relationships between tables. That is it. Do not use that for anything where you actually care what that value is. As long as that value is unique, you should not care what it is.
If you do want sequential numbers, watch this video. I will show you how to make a counter. You can set up a sequential number for every order, every customer, whatever you want. But you are not going to use an auto number for that.
Now the number one reason why that feature exists in Access is so you can split a database apart, have people share it in two different locations, and then bring those records back together without any conflicts. In fact, Access used to have a feature called a replica set a couple of versions ago. They got rid of it, but the concept still remains and it is possible to do it.
In fact, I have got a TechHelp video that explains how to sync two remote databases. You take a database, you copy it, you set up random auto numbers, person A takes it on his trip, person B is still in the office. When the trip is over, they get together, they put the databases back and it syncs everything up nicely.
Now, in a nutshell, if you have got your customer table, for example, design view, here is your customer ID. Right now it is set to increment, which means each new auto number is going to be just the next number in the series. But if you change that to random, Access is going to say once you go random, you cannot go back. So you pick random, you are stuck with it. Change it, yes. Now save the table.
Every new record that you add, come down here, add a new record, is going to get a random auto number. Another record, another record. Sometimes there will be negative numbers.
Now the chances are, if I have added these, and someone else has another copy of this database, and they are also adding records, you should be able to bring them back together and the chances of two of these numbers colliding are infinitesimally small. In fact, in my other video, I did the math. If you added one record every second for a whole year, you would still only have a one in one 36 chance of running into a duplicate value.
So that is why Access has the random auto number option available. There is even another number that is bigger than that, called a UUID or a GUID. I talk about that more in that video too, but if you are interested in that, go watch it.
So that in a nutshell is pretty much the main reason why you would want to use random auto numbers. Generally, your auto number is not something that is exposed to the public, so your customers will not see them because you do not want customers knowing how many customers you have. If someone gets customer number four, they are going to know that you do not have that many other customers, or if they place one order and it is order number 15, and two weeks later, they get order number 17, and they know you only had one order in the middle, then they have the German tank problem.
If you want to learn about the German tank problem and what that is, go watch this video, "Auto Number is Good or Bad." I have all kinds of stuff on auto numbers. I have spent a lot of time working on auto numbers.
So Paxton, there you go. That is why you would want to have random auto numbers. I hope you learned something today, and I will see you next time.Quiz Q1. What is the primary purpose of auto numbers in Microsoft Access?
A. To provide a unique identifier for each record
B. To display record counts to users
C. To make reports look professional
D. To provide user-friendly order IDs
Q2. Why should users not care about the actual value of an auto number?
A. Because auto numbers are always sequential
B. Because the value is only used by Access to ensure uniqueness and relationships
C. Because users can always change the value later
D. Because auto numbers are hidden from everyone
Q3. What is the main benefit of using random auto numbers instead of sequential ones?
A. It makes customer records easier to read
B. It helps with visually tracking order progress
C. It enables multiple database copies to be synchronized without conflicts
D. It saves storage space
Q4. What does Access warn when you change an auto number field from increment to random?
A. You can change back at any time
B. Random mode will generate duplicates
C. Once changed to random, you cannot revert it to sequential
D. Data will be deleted
Q5. What happens if two users are adding records in separate copies of the same Access database with random auto numbers, then later sync?
A. Collisions of auto numbers are very common
B. The database will always reject new records
C. It is highly unlikely that two records will get the same auto number
D. All numbers will be positive
Q6. Why are auto numbers typically not exposed to customers?
A. Because they often contain private data
B. Because they are too short to be meaningful
C. Because they might reveal internal information like how many orders or customers you have
D. Because customers need only their names
Q7. What is a GUID or UUID as mentioned in the video?
A. A visual design for form layouts
B. A larger, more unique identifier than an auto number
C. A password reset tool
D. A formatting option for reports
Q8. If you want to display sequential numbers for orders that users will see, what should you use?
A. Auto Numbers in random mode
B. A custom counter or sequential number system
C. Hide all numbers from users
D. Only use GUID fields
Answers: 1-A; 2-B; 3-C; 4-C; 5-C; 6-C; 7-B; 8-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone covers why you might want to use random auto numbers in your Access databases. I received a question from a member who wondered why anyone would choose to make auto numbers random instead of sequential. This is a common point of confusion, so I want to clarify the main reasons for this feature.
First, it's important to understand that auto numbers exist for internal use by Access. They're designed to ensure every record in your table has a unique identifier, which helps manage relationships between different tables in your database. The actual value of the auto number does not matter unless you have a specific functional need for it. Whether the numbers appear in order or seem random, as long as each one is unique, the database works as intended. So, in most cases, you should not base any business logic on these values or worry about them being sequential.
Now, if truly sequential numbers are important for your needs, such as order numbers or customer IDs you want to display to clients, there are better ways to achieve that. I have a separate tutorial explaining how to create a counter that generates strictly sequential numbers for these cases. It is best not to rely on the auto number field for this purpose.
The main reason Access offers the option for random auto numbers is for database distribution and merging scenarios. Suppose you need to split your database so that multiple people in different locations can work independently for a while and later combine their data. Random auto numbers greatly reduce the likelihood that different people will accidentally create the same ID in their separate copies of the database. This mainly stems from a feature Access used to have called replica sets, which, while no longer officially available, set the precedent for using random numbers for database replication and synchronization.
I cover syncing two remote databases in a TechHelp video. The general process involves copying the database, setting auto numbers to random, and letting two different people add data separately. Later, when the databases are combined, the randomization means the chance of collision between new records is almost zero. Actually, I have done the math on this: even if you add a new record every second for an entire year, the odds of generating a duplicate auto number are just one in 36.
Access also supports another type of unique identifier that is even less likely to collide - the GUID or UUID. I discuss this in more depth in another video if you are curious about those.
As another benefit, keeping your auto numbers hidden from customers protects sensitive information about your business. If customers see sequential numbers, they might deduce how many other customers you have or gain other insights you might prefer to keep private. This relates to a real-world historical issue called the German tank problem, which you can learn more about in my "Auto Number is Good or Bad?" video.
In summary, random auto numbers exist primarily to support distributed database operations and avoid conflicts when merging records. They keep internal keys unique and ensure data integrity, but are generally not for public use or display. If you want more control over numbering, use custom sequential fields instead.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Purpose of random auto numbers in Access
Difference between sequential and random auto numbers
Random auto numbers for preventing conflicting IDs when merging databases
How to change an auto number field to random type
Irreversibility of switching to random auto numbers
Demonstration of random numbers and negative values in Access
Importance of auto numbers being unique identifiers
Scenarios where random auto numbers are useful in multi-user environments
Probabilities of duplicate values with random auto numbers |
