Access Relationship Problem
I have an exercise in which I need to assign one-to-many relationships to some tables in a db.
I can get one such relationship but all the remaining tables form a one-to-one relationship and I don't understand why when they are very similar to the two tables in which I was able to form a one-to-many relationship.
The primary keys have the same data types. In my file a one-to-many relationship exist between the table "tblRentals" and tblRenters". I need to form the same relationship between all the remaining tables except that when I try I get a one-to-one relationship. Why???
I can get one such relationship but all the remaining tables form a one-to-one relationship and I don't understand why when they are very similar to the two tables in which I was able to form a one-to-many relationship.
The primary keys have the same data types. In my file a one-to-many relationship exist between the table "tblRentals" and tblRenters". I need to form the same relationship between all the remaining tables except that when I try I get a one-to-one relationship. Why???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I went to the "tblRentals" and selected the key or foreign key for each of the table and selected "Allow Duplicates" from the connecting tables primary keys which now allows a one-to-many connections. I also enforced referential integrity. Thanks for the help. You both answerd correctly but you got there first so I gave you the points.
So the way I see it now on the table connecting you select a primary key you choose no duplicates for the table and then you choose the table being connected to and place your foreign key there and select allow duplicates. You also would have indexed this field. Is this correct??
So the way I see it now on the table connecting you select a primary key you choose no duplicates for the table and then you choose the table being connected to and place your foreign key there and select allow duplicates. You also would have indexed this field. Is this correct??
<<You also would have indexed this field. Is this correct?? >>
If you enforce Referential Integrity, no as Access (actually JET) will create an index on the many side of the join.
If you create an index with RI enforced, you'll be creating a duplicate index. Note that you won't see the hidden index.
Jim.
If you enforce Referential Integrity, no as Access (actually JET) will create an index on the many side of the join.
If you create an index with RI enforced, you'll be creating a duplicate index. Note that you won't see the hidden index.
Jim.
Actually ... I really didn't mean to say (and Jim is correct about the dup index) "and for the Foreign Key ... do you have Index - Allow Duplicates ?" .... what I meant was ... be SURE that you do not have Indexed - No Duplicates - which will result in a One to One when you try to create a Relationship.
And of course, unless you enforce RI ... you don't really have a meaningful Relationship.
mx
And of course, unless you enforce RI ... you don't really have a meaningful Relationship.
mx
ASKER
Thanks a lot guys. I am new and learning a lot.
If your getting a one to one, then what your doing is joining the primary keys of each table to one another, which is not what you want.
Jim.