Share via

Joining tables in access

Virginia Warbrick 20 Reputation points
2026-04-23T05:12:30.05+00:00

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

Microsoft 365 and Office | Access | Other | Windows

Answer accepted by question author

Teddie-D 16,370 Reputation points Microsoft External Staff Moderator
2026-04-23T05:47:04.4333333+00:00

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.
User's image

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

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.

Was this answer helpful?

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest

  1. AI answer

    2026-04-23T05:12:41.89+00:00

    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:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.