How to create a composite unique index (not as a primary key) in MS Access
  1. Ms Access Append Multiple Tables Into One
  2. Ms Access Merge Two Tables
  3. Access Expression To Combine Two Fields
  4. Ms Access Combine Two Tables
  5. Ms Access Combine Two Tables In Mysql
Ms Access Combine Two Tables
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.

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.

  1. These queries retrieve two different sets of records; one from the Product Orders table and one from the Product Purchases table. The second part of this SQL statement is the UNION keyword which tells Access that this query will combine these two sets of records.
  2. Using the example of daily transaction tables being combined into a weekly table.
  3. The most common type of join is an inner join which is also the default join type in Microsoft Access. Inner Join will display only the rows or records where the joined fields from both tables are equal. This join type looks at those common fields and the data contained within. It only displays the matches.

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.

Two
Column NameDate TypeDescription
IDAutoNumberPrimary Key
Product_NameText
Category_IDNumber

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.

Ms Access Append Multiple Tables Into One

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.comMs access combine two tables in mysql
Other Recent Articles from the MS Access category:
1.Solved: MS Access error 'The text is too long to be edited'
2.Create MS Access Combo Box essential properties by VBA code
3.Create MS Access Combo Box essential properties manually
4.How to do text search in MS Access programmatically
5.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
6.How to easily get a list of field names in MS Access
7.How to count distinct records in MS Access
8.How to do transaction based processing in MS Access
9.How to open a document (local/network file or web page) from MS Access
10.How to use ADOX to create unique composite index - the VBA approach
11.How to do cross-table update queries in MS Access - the right way
12.Three efficient ways to get the number of records by using VBA
13.Use VBA to get the correct number of records in a Recordset object
14.Disable Access Prompt when a record is changed, table deleted, or action queries run
15.How to hide and unhide a MS Access object
16.How to return multiple values from a VBA function (Part 3)
17.How to return multiple values from a VBA function (Part 2)
18.How to return multiple values from a VBA function (Part 1)
19.Three ways to programmatically duplicate a table in MS Access by VBA
20.Create a DLL by CSharp or VB.Net for VBA
21.How to correctly reference and call a DLL
22.How to register a C# or VB.Net DLL
23.Email address validation by Regular Expressions using VBA
24.Fix MS Access error: Query must have at least one destination field
25.How to unselect radio buttons in MS Access after it has been selected
26.How to Change Query Timeout Value for MS Access SQL Queries
27.What is Northwind Traders database

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:

Ms Access Merge Two Tables

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.

Access Expression To Combine Two Fields

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)

Ms access table

Ms Access Combine Two Tables

Next, right-click on the join line between the Orders table and the Order Details table and select Properties from the popup menu.

Ms Access Combine Two Tables In Mysql

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.

Coments are closed

Most Viewed Posts

  • Retroarch Xbox Series S
  • Castlevania Symphony Of The Night Retroarch
  • Top Tv Shows On Amazon Prime

Scroll to top