|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
AutoNumbers Are NOT For You By Richard Rost 
4 years ago
Microsoft Access AutoNumbers Are NOT For You! I get asked a million questions every day about AutoNumbers. How can I keep them in order? Why are there gaps? How can I restore a deleted AutoNumber? How can I keep my customer from seeing their OrderID? This video discusses why AutoNumbers are NOT for you, and you shouldn't rely on their actual value for anything. Links
Keywordsaccess 2016, access 2019, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, autonumbers, dont delete records, deleted autonumbers, restore deleted autonumber, sequential counters, gaps in autonumbers, recover deleted, numbering
IntroIn this video, we will talk about why AutoNumbers in Microsoft Access are meant for the database, not for the user. I will explain the purpose of AutoNumbers as primary keys, why you should not rely on them for sequential order numbers, and the risks of using them for anything users will see. We will discuss common questions about deleting records, gaps in number sequences, and why you should create your own custom fields for visible numbering needs. If you have ever wondered about the role of AutoNumbers, this video will help clear up the confusion.TranscriptWelcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's Fast Tip video, I am going to try to emphasize the fact that AutoNumbers are not for you. I have done several different videos on this topic, but I keep getting asked this question almost daily from people that have all kinds of questions and concerns about AutoNumbers. So let me try to say this one more time, and I will give you a few reasons why AutoNumbers are not for you. Now, I teach all about what AutoNumbers are and how to use them in my Access Level 1 class, my beginner Level 1 class. So if you have not watched this yet, it is free. Go watch it. Even if you think you have been using Access for a couple of years and you know everything, there is no harm in watching my beginner class. Trust me, I cover a lot of fundamentals that even some people who have been using Access for years do not know. I get lots of emails from people, so go watch this. AutoNumbers are used to provide a unique primary key field for each record in the table. You might have a dozen customers named James and even a few named James Kirk, but you will only have one customer ID 2. Only one customer ID 4, for example. These unique numbers ensure that you know exactly and unambiguously which record you are dealing with. If I say go open James Kirk's record, you could have four of them, but if I say go open customer number two, you know exactly who customer number two is. Also very important is that these AutoNumbers are used by Access to form relationships between multiple tables, such as customers and orders, students and what classes they are enrolled in, or products and what vendor you buy them from - relationships between your tables. You, the user, should not care what these numbers are. They could be set to random numbers and it would not make a difference to Access how the database operates, and yes, that is actually an option - you can set AutoNumbers to be random numbers. The AutoNumbers are not for you. You should not care about them, whether it is five, six, seven, eight, two million and four, whatever - you should not care. Access cares. One important thing about AutoNumbers that everyone always complains about: they are not necessarily sequential. By default, they will start at one and count up. However, if you delete a record (which I do not recommend you do), or if another user starts to add a record and then cancels adding it, you may have gaps in your AutoNumber sequence. This should not matter. Do not rely on them being sequential. A lot of people email me because they are trying to keep track of their orders and want them sequential. Do not rely on AutoNumbers for that. I generally recommend you do not delete records at all, especially important pieces of data like customers or orders. You should mark them inactive or cancelled instead. I have a video on that - go watch that one. If you accidentally delete a record, you can get the AutoNumber back, but you have to play a trick to do it. I have a separate video on that as well. This would be something like where you accidentally delete a customer and he has a whole bunch of orders in the system and contacts and other things that are related to that record. You can restore the AutoNumber - there is a way to do it. If you want sequential numbers for anything like orders or customers, if you want sequential numbers, you have to create your own order number field. Do not use an AutoNumber for that. I have a video that shows you how to set that up too - the Counter video. For some things, you may not want customers seeing AutoNumbers. For example, your order numbers. If you have an order number 101 and then, a month later, they get order number 104, then they know you have only had two other orders in the meantime. That brings up something called the German Tank Problem that I talk about in one of my other videos, where the Allies were able to tell exactly how many tanks the Germans had in World War II because they would capture old parts and see what the serial numbers on the parts were. Bottom line is, for some things, you might want sequential numbers. For other things, you do not want them. So you do not want your customers seeing all your AutoNumbers. Sometimes you do want to display a count, like if you want to display a count of the products that are listed on an order, or the students in a class. I show you how to set that up with record numbering. There is another video. Go watch - do not use an AutoNumber for that. Another thing with AutoNumbers: People always ask me if they should use a natural number as their primary key, like a Social Security number or a Vehicle Identification Number. I always say you should use an AutoNumber for that. I have another great video, AutoNumbers: Good or Bad. It has lots of reasons why you should use AutoNumbers and some reasons why you might not want to. Watch that video too. Then other people have emailed me and asked, "Well, if AutoNumbers should not be visible, and you do not want people seeing them, why do you teach them in your beginner class?" It is because it is easier for beginners to understand that an AutoNumber starts at one and just counts up. But in my later classes, I do show you how to hide those things - hide the customer number, hide the order number. It is easier. We have to walk before we run, folks. But the bottom line is, those AutoNumbers are not for you. Do not worry what they are. You could have customer number six. You could be customer number 14. Those AutoNumbers are for making relationships - relationships between tables and doing things like opening up a specific customer from a customer list. Those are things that Access cares about. You can hide that customer ID completely, and the workings of the database will make absolutely no difference. If you want a custom order number, make your own. If you want sequential record numbering, make your own. I have videos that show you how to do all that stuff. It is all on my website, AccessLearningZone.com. I will put links to all of these videos that you can click on in the description below the video, so go down there and click on them. Go watch all these other videos. Now, when anyone asks me anything about AutoNumbers, I am just pointing them to this and saying "They are not for you. Watch this video." I get the same questions every day. I love you guys. I hope you learned something. We will see you soon. QuizQ1. What is the main purpose of AutoNumbers in Microsoft Access?A. To provide a unique primary key for each record B. To keep track of record creation dates C. To count the number of records D. To generate custom order numbers for customers Q2. Why should users not care about the value of AutoNumbers? A. The numbers can be changed by users at any time B. The numbers only serve as unique identifiers for Access, not for users C. The numbers are always displayed to customers D. The numbers indicate the order records were added Q3. Which of the following is NOT a recommended use of AutoNumbers? A. Creating relationships between tables B. Generating a unique identifier for records C. Keeping track of sequential order numbers for customers D. Setting random unique values for primary keys Q4. What issue may arise if a record is deleted or an addition is canceled when using AutoNumbers? A. AutoNumbers will start over from one B. Gaps may appear in the sequence of AutoNumbers C. The table will be deleted D. Access will generate duplicate AutoNumbers Q5. If you want truly sequential order numbers for your customers, what should you do? A. Use the built-in AutoNumber field B. Rely on default primary keys C. Create your own custom order number field D. Change the AutoNumber type to text Q6. Why might you NOT want customers to see AutoNumbers, especially for order numbers? A. Customers could guess how many orders you have processed B. Customers might change the numbers themselves C. AutoNumbers are always random D. AutoNumbers are used only for products Q7. What does the "German Tank Problem" illustrate in the context of AutoNumbers? A. AutoNumbers should always be hidden from users B. Sequential numbers can reveal sensitive information about business activity C. AutoNumbers can only be random D. AutoNumbers are required by law Q8. What should you do instead of deleting important records like customers or orders? A. Delete them to keep the database clean B. Mark them as inactive or cancelled C. Change their primary keys D. Export them to another database Q9. Why does the instructor mention that beginners should learn about visible AutoNumbers in early classes? A. Beginners should manage primary keys manually B. It is easier to understand how records are counted C. AutoNumbers must always be shown to users D. Sequential numbers look better Q10. If you want to display the number of products on an order or students in a class, what should you use? A. AutoNumbers as the display value B. Custom record numbering techniques C. Social security numbers D. Natural numbers as primary keys Q11. Why does the instructor say, "AutoNumbers are not for you"? A. Because only advanced users should use them B. Because they exist solely for Access to identify records uniquely C. Because they should not be used at all D. Because they make the database slower Answers: 1-A; 2-B; 3-C; 4-B; 5-C; 6-A; 7-B; 8-B; 9-B; 10-B; 11-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. SummaryToday's video from Access Learning Zone covers a topic that I get questions about almost every day: AutoNumbers in Microsoft Access and why they are not intended for you, the user. I have addressed this subject in several previous videos, but it continues to be a source of confusion, so let me clarify again and provide several reasons why you should not be concerned with AutoNumbers.If you are not already familiar with what AutoNumbers are and how they work, I recommend checking out my Access Level 1 class. This beginner course is free and goes over all of the fundamentals, including AutoNumbers. Even if you feel confident with Access, it is worth a look because I often cover basics that experienced users overlook. The main purpose of an AutoNumber in Access is to create a unique primary key for each record in your table. For instance, you might have customers who share the same name, perhaps even several named James Kirk, but there is only ever one record with customer ID 2, one with customer ID 4, and so on. These unique identifiers remove any ambiguity in identifying records. If you are asked to open customer number two, you know exactly which record to access—there is no confusion, regardless of name duplication. AutoNumbers are also critical in establishing relationships between tables. For example, they allow you to link customers with their orders, students with their enrolled classes, and products with their vendors. This interconnection keeps your database working properly. What you need to remember is that the actual value of the AutoNumber is not something you should be concerned with. Its value can be set to increment in a sequence or even assigned randomly, if you choose. This flexibility is because the specific number used by Access is meant for the database's internal tracking, not for your own organizational or business purposes. One common complaint is that AutoNumbers are not always perfectly sequential. While they usually start at one and go up, if you delete a record or if a user begins to enter a record and cancels, gaps will appear in your sequence. This is by design and nothing to worry about. You should not expect AutoNumbers to be a gap-free numerical record of your data and should never rely on them being consecutive. For instance, many users want to use AutoNumbers to keep track of their order numbers sequentially. This is not what AutoNumbers are for. Generally, I recommend that you never delete important records like customers or orders. Instead, mark those entries as inactive or cancelled. If you do accidentally remove a record and want to reclaim its AutoNumber, there are methods for doing so, which I demonstrate in another video. If having perfectly sequential numbers is important, such as for order or customer tracking visible to clients, you need to set up a separate order number field of your own. AutoNumbers are not intended for this; I have a video that explains how to create your own sequential numbering system. It is also important to consider what you show your customers. Suppose a customer receives an order with number 101 and later gets one numbered 104. They can infer that only two orders were made in between, which might not be information you want them to have. This issue is similar to what is known as the German Tank Problem, a topic I address in another video, where analyzing serial numbers allowed for military intelligence during World War II. In some cases, such as listing product counts or numbering students in a class, you might want to display sequential numbers on a form or report. For these situations, you should employ a numbering system designed for display rather than relying on AutoNumbers. Again, I have lessons dedicated to these techniques. Another question I get is whether you should use natural data such as Social Security numbers or Vehicle Identification Numbers as the primary key. My answer is that you should stick with AutoNumbers for your primary keys. There is a video on my website that weighs the pros and cons of using AutoNumbers as primary keys, and it is worth watching for a deeper discussion. Some may also wonder if it makes sense to teach AutoNumbers visibly in beginner classes if users are not supposed to see them in a finished product. The reason is that seeing how AutoNumbers increment helps beginners understand the structure of databases more easily. In more advanced classes, I show you how to hide AutoNumbers from forms and reports as needed. Learning the basics comes first; refining presentation comes later. Ultimately, the key takeaway is not to worry about the values of AutoNumbers or try to use them for sequential numbering that your users or customers see. They exist to maintain relationships between records behind the scenes and to ensure database integrity. If you want your own numbering system for things like orders or product lists, you will need to create it yourself. I offer various videos that show you how to set up custom numbering and record counting functionality, all available on my website at AccessLearningZone.com. I will provide links to these helpful videos on my site, so feel free to check them out if you want detailed, step-by-step guides to any of these topics. For a complete video walkthrough, including demonstrations of everything discussed here, visit my website at the link below. Live long and prosper, my friends. Topic ListPurpose of AutoNumbers as unique primary keysAutoNumbers for table relationships in Access AutoNumbers are not intended for user reference Non-sequential nature of AutoNumbers Why not to rely on AutoNumbers for numbering orders Recommendation against deleting records in Access How AutoNumbers are affected by record deletion Why to create custom sequential numbers Reasons to hide AutoNumbers from end users Difference between AutoNumbers and natural keys When to use AutoNumbers as primary keys Why beginners are shown AutoNumbers in classes How to hide AutoNumbers in forms and reports |
||||||||||||||||
|
| |||
| Keywords: FastTips Access autonumbers, dont delete records, deleted autonumbers, restore deleted autonumber, sequential counters, gaps in autonumbers, recover deleted, numbering PermaLink Microsoft Access AutoNumbers Are NOT For You |
