Link to home
Create AccountLog in
Avatar of ghaphis
ghaphis

asked on

Change one-to-one relationship to one-to-many relationship

Hi ,

I made a relationship between two tables in my database in the Relationships window. The relationship should be one-to-many, but Access keeps assigning it as one-to-one. The structure of the tables is as follows:

mother table
- primary key = surveyid

child table
- primary key and foreign key = surveyid
- primary key = row number

Right now when I enter data into the child table, I am only allowed to enter one row and it gives me a primary key violation when I try to enter a second row. How can I resolve this? Should I try to change the relationship type, and if so how do I do that?

Thanks.
Avatar of JamesCronshaw1
JamesCronshaw1
Flag of Luxembourg image

Change your child table to drop the SurveyID from the Primary key, as it appears this is not required (though I could be wrong here dependant on your logic).  

In your Relationships window, select the relationship thats causing you the issue and double-click it to open the change dialog.  You can then make changes you wish to it (remembering to save of course!)
I am not sure what is wrong here. All your design sounds fine. to proof the concept I have knocked up a simple .mdb that illustrates this.

I suspect that something went astray when you created the key for the second table or the relationship.

MotherChild.zip
Create this:
mother table
- primary key = surveyid

child table
- primary key = autonumber
- foreign key = surveyid

Cheers
JC
Further...

mother table
- primary key = surveyid <<<OK

child table
- primary key and foreign key = surveyid <<< This will create a 1 to 1 relationship
- primary key = row number <<< Cannot have TWO primary keys in same table.

Cheers
JC
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
"automatically create a (hidden) Index on the foreign table."

>> s/b automatically create a (hidden) Index on the foreign KEY.

mx