Learning about Microsoft Access and applying the primary key to a table is the least you must add to your table designs. Most database developers would have used this concept and know you can have only one primary key to a table which in itself is a form of indexing in Access.
Take a look at the quick video tutorial (less than 3 minutes) which demonstrates using version 2010 how to set more than one indexed field to a table using the indexing dialog box.
Indexing In Access – Setting More Than One Index To A Table
Unlike primary keys, you can optionally set these indexed fields to a ‘Null’ value (a value with no value to it, which is left empty) and also allow duplicate values to be captured too. These types of field are referred as foreign or secondary keys and will help to improve the performance of your queries.
You will need to think about which set of fields in your table design that you intend to mark as indexed and to help you along with this decision the following may help:
- Choose a field which is popular as criteria for your Access query.
- Choose a field which is going to part of a calculation or expression elsewhere.
- Choose a field that is the ‘many’ side to a related table where values can and are duplicated.
- Choose a field that you do a lot of sorting against.
Remember, you can always remove these types of indexing in Access if the general performance of your database starts to slow down as it will harm the structure of your database.
Learn to build a database properly by spending some time and investment into some sort of reference guide maybe a wise move and I’m here to help and support you!
Tags: Access, creating multiple indexes, how to set an index to a table, indexing in access, primary keys, table design index keys
From memory, you can have up to 10 fields indexed to an Access table. That was certainly the case with the eariler versions (pre 2007).
Hi Ben,
I have just bought your ebook, because I have a designed a database to sit in Cloud, but I need my potential customers to sign in and out as they need without my intervention.
In Access 2003 there was an option to enable people to create their own password but now, apparently that option has been disabled in 2007 and 2010, I have watched your videos but they do not cover this, can you help?
Thanks
Wanda
Hi Wanda,
Many thanks for your custom.
You are correct about Access 2003 security (for MDB files) where users could manage their own passwords but with the release of Access 2007 and 2010 using a new file format (accdb) and a newer engine (JET 4/ACE), workgroup security was dropped leaving one of two other options:
1. Utilising SharePoint services for the web or
2. Building your own utility hence why I started to build and offer this to others.
It’s the beginning of something you can then customise and redevelop using VBA and ADO as I have deliberately left this source open to be edited further.
I have been asked for a richer set of tools to this utility offer (https://accessdatabasetutorial.com/offer/offers/) but haven’t yet developed it and their other potential coding and functional challenges to this.
If you need to develop this further, I do offer online coaching (https://accessdatabasetutorial.com/offer/microsoft-access-courses-online-offer/) where we can work ion this together if this is of interest to you?
What are your thoughts here?
Regards
Ben