Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If you perform a lot of searches on one field, you can tell Access to index that field, and index is a stored sort of data, so that when you do a search or filter, Access already knows where that information lies. This speeds up your search results, particularly when you're working with rare or unusual pieces of information within a large number of records. Access's index works a lot like looking up a word in the index at the back of the book, so you can find that term within the chapters. But just like an index in the back of the book, you don't need to turn on a field's index if much of the data is identical.
That would be like looking up the word 'the,' if the data is everywhere then index is of no benefit at all. Your primary key is automatically indexed; you don't have to turn it on manually. I'm in my Customers table and I'll click on the Company field and then I'll go up to the Table tools ribbon and choose Fields and there is a check box for Indexed on the far right. You can also turn on the index from the Design View. I'll go over to the far left and click on the Design button, and then I'll click on the Company field.
Down here in the Field properties there is a row for Indexed, and it says 'Yes'. It also says Duplicates OK, and in our case that's correct because we can have two branches of the same restaurant as customers. But if every one of the entries in this field needs to be distinct, you can click on the dropdown on the far right and change it to 'Yes' indexed, but No Duplicates. I'm going to leave it with Duplicates OK. To see all indexes that you have in a table, go up to the Table tools, Design ribbon and click on the Index's button, you'll see a list of all the fields you have indexed and you can manage them or even delete them from here.
There is one last consideration; you don't want to do too much indexing. Turning on indexes makes your database run more slowly, because every time you add a new record or you change your company name Access has to update the index before it can continue, but if you frequently reference a field with distinct information in a large table, then index will shave precious seconds off of all your search times.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64672 Viewers
80 Video lessons · 124298 Viewers
52 Video lessons · 60237 Viewers
59 Video lessons · 46066 Viewers