Microsoft Access 2010 Tutorials
Working with Datasheet View / Using Auto Numbering
Visitors to VTC.com will be able to view all introductory videos for each training course.
Free Trial Members will gain access to first three chapters for each training course.
Full Access Members have full access to VTC.com�s entire library of video tutorials.
Learn More
Subtitles of the Movie (Microsoft Access 2010 / Working with Datasheet View / Using Auto Numbering)
In this video I want to talk to you about Auto Numbering on our fields, and in our records. And the way I want to show you this, is first of all, notice this is something I've been kind of ignoring, it's been on the screen, and you might have been wandering about it, but if I go to the Create Tab, and click on Table to create a new table, Microsoft drops me into Data Sheet View, and they have this fascination with this ID column. Now they're trying to help me, and they're trying to make it easy, and let me show you what that does. If I click here, and I tell it that I'm going to store text, and I just call this F name, so for first name, and input that. Now I can come right here, and immediately start inputting data into the table, but notice if I type Mark, it automatically changed that to one. And I Tab, and I go to the next line, as soon as I start typing, there is Jack, there's two, Fred is three, you see where this going right? You see the pattern? It is going to automatically number these fields for me, OK? And I can't go back, notice I'm trying to change that, and you hear the, you hear it squawking at me, let me turn it up just a little bit. Anytime I try to change it, and right down here at the bottom, it says, Control can't be edited. It's bound to the Auto Number field ID alright? Well, let me save this table, I'll just save it as table one, then I'll go back and open it in Design View, notice that has that data type of Auto Number, and then new values show up here as increment. That's kind of cool that it will do that, but keep in mind, this becomes a lazy persons way to build a database, because people say, oh well, rather than look out here for a unique key for my primary key, I'll just do this, and this will maintain, you know, the primary key, and every row is unique. Well what does unique mean? If these ID numbers that it's putting in, don't have any direct relation or meaning to this data out here, now I'm now storing data that has no meaning, and that is going to come back to bite me at some point. Now is there a place where we can use Auto Number in our design? Yes there is. And this is when you should use it. Notice in the instructor table, I want a unique instructor ID for every instructor. Now I'm going to manually put these in as one, two, three, four, five, six, and on the IID, or the instrument ID, I want a unique instrument ID attached to every row, so that I can come back later and figure out what instrument that is, so say I've just put in one for piano, the next row, for example, I can put in, if we add an instructor in here, I can add instructor number two, the IID I can make that two, I can make it whatever I want, and I can say this guy teaches guitar OK? And this is Jack Jones, and I can start to put his information in, 615878873, it's amazing how I know these numbers isn't it? Man I'm sharp OK? And so notice now, I have a two in here. Could I Auto Number these? Absolutely alright? But here's the deal, I can't change to Auto Number now, unless I go back and delete these rows out, and we save my data here, and come back, and right click, and go to Design View. Notice if I try to choose Auto Number now, it's going to say, wait a minute, once you enter data into a table, you can't change it to Auto Number OK? There's another few issues with Auto Number that you'll run into, I won't go into them now, but just kind of trust me on this. Be very careful with Auto Numbering alright? What I usually do on the data sheet, if I'm going to input data there directly on a new table, which is rare, I will just delete that column that, skip it, and add my own columns OK? So that's enough about Auto Number. Go out and read about Auto Number in some of the documentation on Microsoft's site. Read about it out there on some of the blogs and Google, and so forth. Auto Number is cool, but you know, it's kind of like having a car that'll go 140 miles an hour, it's cool that you've got it, and maybe there's going to be one or two times in the future when you need to go 140 miles an hour, but for the most part, it kind of tends to just be to dangerous. So that's my take on that, and you may feel a little differently about that, and you may use it more, but just understand what it's there for, and understand what it's not there for. Don't let it become a lazy primary key generator for you. Now if you're working with data warehouses and that sort of thing, a lot of times, you will do that, but we're not data warehousing here alright? So anyway, that's enough confusion. Just be very careful with that whole Auto Number scenario.
Tutorial Information
| Course: | Microsoft Access 2010 |
| Author: | Mark Long |
| SKU: | 34224 |
| ISBN: | 1-936334-91-7 |
| Release Date: | 2011-05-12 |
| Duration: | 9 hrs / 121 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
