How an Access 2007 Table Index Works
An Access 2007 table index is a mirror of the table that’s simply sorted by one or more of the tables fields. This makes queries and reports faster because Access 2007 already knows where the information is in the table and can simply go get it without having to search through the entire table looking for it. Again, this is much like the index at the back of a book.
Suppose, for example, that you have a large database of customer information that includes the customers’ names, addresses, cities, states, and phone numbers. Now suppose that you often generate a report to organize the information for a print advertisement you will be sending each customer by mail.
You also want to separate the customers by state because residents of different states will be receiving a slightly different advertisement. By creating an index for the “state" field in the table, all of your Arizona customers, for example, will be next to one another which will speed up queries and reports generated for customers by state.
Of course, you could do the same for “city" by indexing that field instead in case you wanted to query or generate reports based on customer’s city or town. Understand that without an index to help Access 2007 find data, the application would have to search through every record each time a query or report is generated in order to pick out the data requested.
In relatively small Access 2007 database, indexing does not significantly decrease query and report generation times. Indexing is really a function for databases that contain many thousands of records. Still, as your database grows and you notice that your queries and reports are taking longer to complete, it is good to know there is a function that can help speed things up.