Indexing Fields in Microsoft Access Tables
Indexing is one of those features that is almost never used correcly by new Access users. You can get away without indexing anything in a small database. However as your database grows, you're going to want it to keep things running smoothly. As your database gets bigger and bigger, it's going to get slower and slower if your tables aren't properly indexed.
Recommended Course
Links
More Info
The first use of Indexing is to prevent duplicate values. Obviously you want to make sure your Primary Key is indexed so that you don't have two customers with CustomerID 3, for example. Access will automatically index an AutoNumber field.
You may also want to indexing it for any field you want to uniquely identify a record with. For example, I index email address as no duplicates. There can be one and only one customer with [email protected], for example. Some other companies may index phone number to uniquely identify customers.
You can use Yes (Duplicates OK) to have Access index the field for the purposes of speeding up searches and sorts. For example, let's say you do a lot of searching for customers based on their last name. You may want to index that field, but allow duplicates. You still want to allow multiple people named "Smith," but because you search on it a lot, you want to index it.
See, when you enter records into a table, the data is stored in no particular order. So if you want to sort this list, it's slow. If you want to search for a single name, Access has to start at the top, and run through all the records until it finds what you're looking for. Imagine how difficult it would be to find one person in an unsorted phone book (remember those?)
But when you index a field, Access creates a separate index table (it's hidden, you don't see it) that it manages. It's sorted by that field, and this greatly speeds up searches and sorts based on that field.
Don't index too many fields, though. Doing so will increase the size of your database needlessly. And, indexing slows down updates and appends because Access has to rebuild that index table every time you make changes. In fact, if you do more data entry and editing than lookups, you may want to consider not indexing any fields. If you have a dozen people doing data entry all day, and you only run reports on that information once a month, go easy on the indexes. It's all situational based on the needs of your business.
You can index Short Text, Long Text, Number, Date/Time, AutoNumber, Yes/No, and Hyperlink fields. You cannot index any of the field types that you shouldn't be using anyways: OLE Object, Calculated, Attachment. In older versions of Access you didn't used to be able to index Memo fields, which are now called Long Text fields, or Hyperlink fields. Honestly, I almost never index Long Text as that's going to be a real performance hit on your database.
By default, Access will automatically index fields that end in ID, key, code, or num. You can disable this under File - Access Options - Object Designers - AutoIndex on Import/Create.
You can view all of the indexes in a table by clicking on the Indexes button on the Table Design menu. In here you can create something called a multi-field index. Let's say you routinely search or sort based on last name and first name together. Creating a multi-field index means that your database now has an index built containing these two fields together, and doesn't have to search two separate indexes. Again, this only makes sense if you've got a zillion records, but in a big database, it can greatly improve performance. See my separate video for details on multi-field indexes.
The only time I ever come in here is to create something called a Composite Key which is an index based on two fields, such as not having the same product twice on an order.
If you want to learn more about indexing, I cover it in my Access Beginner Level 4 class. I walk you through all the different fields in my database and explain which ones I index and why. I also cover a whole bunch of other field properties, compact & repair, backing up your database, and lots more.
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, index, indexing, indexed, indices, primary key, foreign key, yes (no duplicates), yes (duplicates ok), AutoIndex, How to Create Index for a Table, Creating Indexes, Indexing a Field, unique index, What is indexing, How do you use an index
16 months ago
