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.
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.
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"automatically create a (hidden) Index on the foreign table."
>> s/b automatically create a (hidden) Index on the foreign KEY.
mx
>> s/b automatically create a (hidden) Index on the foreign KEY.
mx
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!)