Microsoft Access 2010 Tutorials
Working with Tables / Adding an Index
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 Tables / Adding an Index)
One other thing that we're probably going to need to do to our tables is to choose where we're going to put an index. Now let's talk about indexes specifically as they pertain to Access tables. Now in another movie in this course, that was entitled Indexes, I talked generally about how an index works on a database table, and what it does for you, and then kind of the expense that you pay for having indexes. And in this movie, I want to talk about exactly how to set an index on the students table, so the first thing I'm going to do is right click, and go into Design View on the students table. And notice we've already set the primary key on the SID column, or the student ID column. Now if yours is not set and not a big deal, and I'll show you how hard is to set or remove these primary keys. Just right click on the field name over on the left, and either click on Primary Key to turn it on, or click on it to turn it off. It just toggles. But what I want you to notice is, is the primary key itself is an index. If you'll look down here, you'll notice that there is no index on SID, but if I right click, and set the primary key, you'll notice that it becomes an index, yes, with no duplicates. And so the process of making sure that we don't have any duplicates, is itself what's called an unique index. Every item in that SID column in this table, has to be unique from all other items, not one can be the same. Well, we can also find ourselves needing to add another index to our table, for example, let's say we query this table quite a bit on the last name field. Well, we might want to put an index on there to speed those up, and so if you'll come down here and notice, I clicked on last name, I come down here, and look at Indexed, and there's no index on it. Well what I can do is, just click in this area right here, and come over here to the drop down, and choose Yes, allowing duplicates, or yes not allowing duplicates. Now no duplicates basically means a unique index, and if I allow duplicates, that's says OK, this is going to set this table with an index order on it, and if there are duplicate fields, that's OK, we'll just list them one after another. And that easily, I put an index on last name. Now notice I'm going to also put one City here. And on City I will do an index, and I will not allow duplicates, but when you first just look at this table, you really can't tell what indexes are here. Well, notice if I go to the Design Tab, and right here under Show Hide, there is an Indexes button right? So if I click on that, it shows me everywhere that I have indexes, and I can remove them right here. I can just right click, and delete these rows, and that removes those indexes. That's the same as coming down here on City and State, and setting index equal to no. So just remember that, if I set it yes here, this is as hard as it is to turn an index on. Now when I save this table, it will re-build it, and if I want to turn that off, I can just come down and click on this, and just say no, it's not indexed. Now I can also just double click on indexed, you see how I'm double clicking, and it's just scrolling through the list alright? Now I want to show you one more thing before we leave. What if I want to do an index on last name and first name, so I want to always index on last name, but if I have two Smiths, then let's include the first names in on that list. Well, I'll show you how to do that here in Access. Click on Indexes, and I'm just going to call this last first, that's the name of the index, and the first field is last name, and it's ascending from A to Z, and then notice I don't give it a name here. I just come over and choose first name, and it's ascending A to Z. And then I just close that out, and then when I try to close my table, it's going to ask me do I want to save these changes. Now again, this is kind of weird, because if you look at first name here, it doesn't show that there's an index on it, and if you look at last name, it doesn't show. You have to click on Indexes to see that, that's there alright? Now also on these indexes, notice, that I can choose whether it's the primary or not, and if I try to choose primary here, it's later going to either ask me, or just move it, and so you want to be careful, and it also shows you there, that, that is a multiple column deal. So I'm going to take this back off, and then I'll come back up here on this one, and choose primary on that, choose primary there and it's right back OK? So notice now I have my primary key on SID, and I can do these obviously ascending or descending, I'll leave it ascending, and this one is a multiple column index, on first and last, and I can do up to ten columns in one particular index. Be very careful with that OK? So that's how to do create indexes and do multiple column indexes. You need these on columns that are queried quite a bit, and also let me say one more thing before I leave this. Indexes are much more valuable when you have thousands of rows in your table. Just a couple of hundred of rows, not going to be a big deal OK? This is for really large, but the thing to to is, to test these, and see if you're getting better response on your queries, and then you'll know that your index is really helping.
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
