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.
Access provides a special type of query that you can use to vertically splice together the data from two or more tables. The tables don’t even need to have the same fields or fields of exactly the same data types. This is the union query, which can be constructed only by using the SQL View pane in the query designer.
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|
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!
|Other Recent Articles from the MS Access category:|
This MSAccess tutorial explains how to create a query that joins 3 tables using LEFT JOINS in Access 2003 (with screenshots and step-by-step instructions).
Question: In Microsoft Access 2003/XP/2000/97, how do you link 3 tables together, where table 1 is linked on one field to table 2, and the table 3 is linked to table 2 on another field?
In the result, I want the query to display columns from table 1, 2, 3 having table 1 as the primary one. I want all the corresponding records from all tables and zeros for empty records. Also the desired columns from the 3 tables should show.
Answer: To retrieve your data, you will need to perform multiple left or right joins. In this example, we'll demonstrate how to accomplish this query using left joins.
For the purposes of this example, we'll assume that:
Table 1 = Employees
Table 2 = Orders
Table 3 = Order Details
First, create a new query and add all 3 tables to the query.
Right click on the 'join line' between the Employees table and the Orders Table and select 'Properties' from the popup menu.
When the Join Properties window appears, select the second option and click on the OK button.
Your query should now look like this. You will notice an arrow on the right-side of the join line. (between the Employees table and the Orders table)
Next, right-click on the join line between the Orders table and the Order Details table and select Properties from the popup menu.
When the Properties window appears, select the second option and click on the OK button.
Next, select the fields that you want in the query results. We've chosen the fields below.
Now when you run the query, you will see blank values in the Order Date and Quantity fields. This is because there were no matching records in the corresponding tables based on the join criteria.