Custom Search
 


How to create a composite unique index (not as a primary key) in MS Access



This article applies to Microsoft Office Access 2003 and above.

It's a very useful practice that we sometimes need to store unique data across multiple columns in a table. One way of doing this is to create a primary key which contains these columns, but what if we already got a primary key (such as an AutoNumber field) in the table and we still want to keep and use it? The answer is to create a unique composite index on these columns. This type of index prevents duplicate values from being entered into the combination of these columns.

This article shows you the steps to create a unique composite index. Each composite unique index in Access can contain up to 10 columns.

The table used in this demo has three columns: ID, Product_Name, Category_ID.

We want to create a composite unique index on column Product_Name and Category_ID.

Column Name Date Type Description
ID AutoNumber Primary Key
Product_Name Text  
Category_ID Number  

We want to make sure there are no duplicate values in Product_Name column in the same category, so we need to define a composite unique index on column Product_Name and Category_ID. This way, when data are added to this table, Access Jet engine checks the data and prevents duplicate values from being entered into column Product_Name and Category_ID as a whole.

1. Open the table in Design View.

Open your Microsoft Access database. Click the Tables menu to see all the tables in the database. Find the table you want to add the index and right click on the table name, then select Design View.

2. Click Indexes button.

On the toolbar menu across the top, click the Indexes button to open the window that has the list of fields on which the table can be indexed.

3. Enter the first column for the index.

The Indexes window is opened as shown below. Note that the primary key column has already been added to the first row automatically.

In the second row of the indexes window, enter the Index Name as Uidx_Products (U stands for unique) and select Product_Name from the dropdown list for Field Name. Use Ascending as the Sort Order.

4. Enter the second column for the index.

Next, in the third row, don't enter anything in the Index Name field (leave it blank). In the Field Name, select Category_ID in the dropdown list and Use Ascending for Sort Order.

Here is what it looks after the third row is filled. If you have more than two columns for the composite unique index, repeat this step to add more columns for Field Name.

Each unique composite index in Access can contain up to 10 columns.

5. Specify the Unique property for the index.

Now it comes to the crucial part. Click the Index Name cell for Uidx_Products. Then, in the Index Properties section of the window, select Yes in the dropdown for the Unique property. This will enforce data uniqueness for the combination of column Product_Name and Category_ID.

Here is what it looks after all the values are entered. You can now close this window and save the table design.

6. Test the unique composite index.

Now open the table again but this time open in Datasheet view. Enter some values in the table to test. The screenshot below shows that I entered Chocolade twice for Category_ID 1 and Access prompted the error message because the last value I entered was a duplicated entry which is the same as the first value for Category_ID 1. You may notice that the third value is also Chocolade but Access accepted it because its Category_ID is 2.

Happy Indexing!


Copyright© GeeksEngine.com



Other Recent Articles from the MS Access category:

1.Examples of MS Access DateDiff function used in query and VBA code
2.MS Access DateDiff function
3.How to find out your computer name and username by VBA
4.Examples of MS Access DatePart function
5.MS Access DatePart function
6.Examples of MS Access DateAdd function
7.MS Access DateAdd function
8.IIF function basics - the CASE statement of MS Access
9.MS Access Date Expression
10.Solved: MS Access error "The text is too long to be edited"
11.Create MS Access Combo Box essential properties by VBA code
12.Create MS Access Combo Box essential properties manually
13.How to do text search in MS Access programmatically
14.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
15.How to easily get a list of field names in MS Access
16.How to count distinct records in MS Access
17.How to do transaction based processing in MS Access
18.How to open a document (local/network file or web page) from MS Access
19.How to use ADOX to create unique composite index - the VBA approach
20.How to do cross-table update queries in MS Access - the right way
21.Three efficient ways to get the number of records by using VBA
22.Use VBA to get the correct number of records in a Recordset object
23.Disable Access Prompt when a record is changed, table deleted, or action queries run
24.How to hide and unhide a MS Access object
25.How to return multiple values from a VBA function (Part 3)
26.How to return multiple values from a VBA function (Part 2)
27.How to return multiple values from a VBA function (Part 1)
28.Three ways to programmatically duplicate a table in MS Access by VBA
29.Create a DLL by CSharp or VB.Net for VBA
30.How to correctly reference and call a DLL
31.How to register a C# or VB.Net DLL
32.Email address validation by Regular Expressions using VBA
33.Fix MS Access error: Query must have at least one destination field
34.How to unselect radio buttons in MS Access after it has been selected
35.How to Change Query Timeout Value for MS Access SQL Queries
36.What is Northwind Traders database

Copyright © 2024 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy