Joining tables in access

Question

No unique index found for the referenced field in the primary table

trying to join two tables for the same field name
No data has been entered

Answer 1

Hi @Virginia Warbrick
In Access, you cannot establish a relationship between tables unless the referenced table has a primary key or a unique index. Without one, Access has no way to enforce referential integrity. To fix this, you need to define a primary key on the table you want to reference first. Once that’s in place, you can create the relationship successfully.

You can follow the steps suggested in Q&A Assist's answer.

I hope this helps.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".          

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The error means Access cannot create a referential relationship because the referenced field in the “primary” table is not indexed as unique (typically not a primary key).

To fix it when joining two tables on the same field name:

1. Open the primary table in Design View.
2. Locate the field that should be referenced (for example, an ID field).
3. Make it a primary key or set a unique index:
   • To use it as primary key: select the field and click Primary Key on the ribbon.
   • Or set Indexed to Yes (No Duplicates) in the field properties.
4. Save the table design.
5. Now create the relationship/join:
   • Open Database Tools → Relationships.
   • Add both tables.
   • Drag the primary table’s key field to the related field in the other table.
   • In Edit Relationships, check Enforce Referential Integrity if needed, then click Create.

Notes:

• The related field in the second table must have the same data type and contain the same kind of data as the primary table’s key field.
• The related field does not need to be unique; only the referenced field in the primary table must be unique.

Once the relationship exists, Access will automatically create the appropriate join in queries when both tables are added.

---

References:

• Join tables and queries
• Access SQL: FROM clause
• INNER JOIN operation (Microsoft Access SQL)
• Relationship must be on the same number of fields with the same data types. (Error 3368) - Microsoft Q&A