13 hours ago: Power and Internet are out in my office due to Hurricane Ian. Click for details.
 
Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
Home > Fast Tips > Access > Indexing < No Data | Indexing >
Indexing
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   53 days ago

Indexing Fields in Microsoft Access Tables


 S  M  L  XL  FS  |  Slo  Reg  Fast  2x  |  Bookmark Join Now

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

Learn More

FREE Access Beginner Level 1
Access Level 2 for $1.00

Free Templates

TechHelp Free Templates
Blank Template
Contact Management
Order Entry & Invoicing
More Access Templates

Resources

Mailing List - Get emails when new videos released
Consulting - Need help with your database
Tip Jar - Your tips are graciously accepted

Questions?

Please feel free to post your questions or comments below or post them in the Forums.

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 amicron@gmail.com, 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

 

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

Check out these other pages that may be of interest to you:

9/27/2022Report Border
9/26/2022First Day of Month
9/23/2022Before Update
9/22/2022Synchronize Subforms
9/21/2022InputBox
9/20/2022Link Master Fields
9/19/2022DateSerial
9/18/2022Sequential Annual Coding
9/16/2022Scope
9/15/2022Resize Forms
 
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: FastTips Access 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  PermaLink  Indexing Fields in Microsoft Access Tables