Share via

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

Anonymous
2014-10-07T13:23:30+00:00

Howdy Folks,

I am attempting to build relationships between multiple tables. I have indexed and ordered each table in the same way. Each table has a PK autonumbered field.

However these aren't the fields I am trying to create the relationship between. The fields (Climate ID) I am trying to create the relationships between are not PK's, they are indexed as "Yes (Duplicates OK), and the data type is short text and contains alphanumeric data. When I attempt to create the relationship and enforce referential integrity I get the "No unique index found for the referenced field of the primary table"

Hope someone can steer me in the right direction

Cheers

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

12 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-10-08T16:09:18+00:00

    If you have a separate table for each element that strongly suggests to me that your design is wrong on the whole. I think taking the time to understand relational database structure would make things a whole lot easier for you. There is a free eBook by Crystal titled "Access Basics" at my website here 

    In terms of getting from point A to point B, where I am at present, this was the only way of dealing with my data for each element. As I say, each element contains varying numbers of records, and varying data as it pertains to climate ID and dates (i.e. each element does not have the same climate ID's or dates). In any case, thanks for the "Access Basics". I will give it a read and hope it helps.

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2014-10-07T15:18:33+00:00

    The list of dates from the "SampleDates" table has 12757 records. The "Climate ID" and "Date" are unique but as mentioned above, there may be repeats of both "Climate ID" and "Date". The "element" tables contain thousands of dates and climate ID's and all I need to do is draw out the corresponding weather data for my "SampleDates" table in one large query.

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2014-10-08T13:53:00+00:00

    Thank you for the response. You are correct. ID in the SampleDate table is not related to the ID in each element table. As well, the combination of Climate ID and Date is not unique. I have cleared the "Enforce Referential Integrity" and started creating relationships. What I have been doing is using outer joins from the main SampleDate table to each element table. I end up having 26 joins as there are 26 element tables with varying data in terms of dates, and climate ID's (not all records have the same number of records and differ in dates or climate ID's). Can anyone tell me if there are any other ways of making these joins/building relationships more efficiently? 

    Cheers

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2014-10-07T15:43:16+00:00

    No, the combination of Climate ID and Date is not unique. However the combination of of the PK, ID, Climate ID, and Date are unique.

    1 person found this answer helpful.
    0 comments
  5. Anonymous
    2014-10-07T15:21:23+00:00

    because there is duplicate data the "Climate ID" and "Date" fields for both my master "SampleDate" table and my "element" tables I cannot use them as primary keys, so how is it possible to have a unique index on the field(s) on one side of the relationship and enforce referential integrity?

    1 person found this answer helpful.
    0 comments