Changing Relationship Type in Access

Question

Hello! I am having a problem in Access where it won't let me enter duplicate values into a particular table (for reference, I am entering the same P_Manager for different orders placed). I get an error that says the table cannot have duplicates and I need to check the index for that particular variable.  I checked and have tried both "no index" and "yes (duplicates ok)".  

I believe the issue isn't the index, but the fact that I have a relationship established with a different table, using this P_Manager as the associated query with the other table.  Currently, the relationship editor says the relationship type is one-to-one.  I believe I want to change it to one-to-many, but there seems to be no way for me to change the relationship type.  Would anyone be able to help me change the relationship type?

Answer 1

Hi Emily, I'm an independent adviser and will try to help. In the Relationships Window you can click on the line between the table to select the relationship. You can then right click on the line and select Edit Join and edit the relationship there. But if relationship was set as 1:1; its likely that both field are set for No Duplicates in both tables. If P_Manager is a field in your Orders table set for Duplicates OK. Then a relationship between other tables shouldn't matter.

So another place to look is in the indexes for the Orders table. You may have a composite index that is causing the problem.

If you need further clarification in this feel free to ask.