Creating one-to-many relationship in Access shows only " Indeterminate" Relationship Type option

Question

So basically I am trying to design my simple Employee/Mission Tracking Database using Access.

I wanted to create a relationship between Employee table and Mission table ( many-to-many). Therefore, I needed to create a bridge-or conjunction- entity, I named it Mission_Assignment. Obviously, PKs of the two related tables ( Mission_ID and Employee_ID) should be included in this bridge entity as foreign keys.

When I tried to join Mission_Assignment with Mission by dragging the PK Mission_ID to the other table, Access didn't show me the option of making this relation as one-to-many, even if I have checked the Enforce refrential integrity box. The only option I got is "Indeterminate" relationship type!

I want to know why this happened? Why can't I have this one-to-many relationship? I tried to change the data type of the PK of the related table using Lookup Wizard, in order to be recognized as a foreign key in the conjunction table, but it didn't work.

Any help would be appreciated.

Answer 1

Check the structure of your [Mission] table. I suspect that [Mission_ID] (or whatever column from [Mission] you are trying to use in the Relationship) is not actually designated as the Primary Key for that table.

Access determines the Relationship Type as follows:

[Table1].[Field]    [Table2].[Field]    Relationship Type
------------------  ------------------  -----------------
PK or Unique        PK or Unique        One-to-One
PK or Unique        Not (PK or Unique)  One-to-Many
Not (PK or Unique)  Not (PK or Unique)  Indeterminate

Answer 2

I had a similar issue attempting to create a relationship between two tables (ReportInfo and Inspection Team) I decided to make ReportNo of datatype Short text, the PK because it was the main link between all other tables. In my ReportInfo Table, I also had an ID that would auto increment and this was initially the PK but it didn't make sense since the other tables required ReportNo as the FK. The Indeterminate option was the only one available when attempting to link with the two tables I mentioned earlier.

As I changed the PK, it solved the issue.

Answer 3

I had similar issue however after reading the above i checked closely and one of the primary key was "indexed: Yes (Duplicate OK)".. I changed this to no duplicates and it allowed the relationship to be established.

thanks for the help.