dev45
asked on
Access 2007, cannot enforce referential integrity
Hello I am having a small problem. I have never used databases and only taken 1 course on it in college.
I am trying to use Access to set up a simple database. At the moment I have 2 tables; PERSON, and ACCOUNT. Let's say this is a bank account. The PERSON table has the usual fields, name, address, phone, email, ect.. and an auto number as a primary key. The ACCOUNT table has 3 fields, Account number, Account holder, and Joint holder. Account number is a numbered primary key here.
Both Account holder, and Joint holder I want to have as Foreign Keys from the PERSON table since two people may have a joint account at a bank. Though when I try to create this relationship I get a access cant enforce referential integrity error.
If I have the Primary Key of PERSON as a foreign key in just Account Holder, I get no referential error. Then if I try to add another relationship from the PERSON table to Joint Holder, access says that a relationship from this table already exits and an option if I still want to create the relationship. Upon clicking yes, I get the access cant enforce referential integrity error.
What database rules am I violating here? Thank You for any help.
I am trying to use Access to set up a simple database. At the moment I have 2 tables; PERSON, and ACCOUNT. Let's say this is a bank account. The PERSON table has the usual fields, name, address, phone, email, ect.. and an auto number as a primary key. The ACCOUNT table has 3 fields, Account number, Account holder, and Joint holder. Account number is a numbered primary key here.
Both Account holder, and Joint holder I want to have as Foreign Keys from the PERSON table since two people may have a joint account at a bank. Though when I try to create this relationship I get a access cant enforce referential integrity error.
If I have the Primary Key of PERSON as a foreign key in just Account Holder, I get no referential error. Then if I try to add another relationship from the PERSON table to Joint Holder, access says that a relationship from this table already exits and an option if I still want to create the relationship. Upon clicking yes, I get the access cant enforce referential integrity error.
What database rules am I violating here? Thank You for any help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, when I say double click on the existing relationship, I mean the joining line between the two tables.
Hi Tramtrak
The procedure you describe will add a second pair of fields to the existing relationship. It will not add a new relationship. This would force every account to have two account ollders, and that those two must be the same!
--
Graham
The procedure you describe will add a second pair of fields to the existing relationship. It will not add a new relationship. This would force every account to have two account ollders, and that those two must be the same!
--
Graham
[Yawn]...yep, guess that's what happens when you're too tired to read the question properly.
What Graham said, drag another instance of PERSON table into your Relationships window and use that for your second relationship.
What Graham said, drag another instance of PERSON table into your Relationships window and use that for your second relationship.
ASKER
Yes Graham that did work. Though the subdatasheets now show up blank. If I remove the 2nd instance of PERSON in ACCOUNT, that is remove Joint Holder from the table then subdatasheets work fine, otherwise they are blank. Any idea why?
Your subforms (PLEASE don't tell me you are entering data directly into the tables in subdatasheets!) cannot be linked via both relationships.
If they are, then the subform/subdatasheet will show only those accounts where BOTH the account holder AND the joint holder are equal to the current person record. Of course, such an account should not exist.
If you want to have a subform to show all accounts where the current person is either the primary holder or the joint holder, then I can't think how you could do it with automatic master/child linking. You would need to set a filter on the subform in the Current event of the main form.
--
Graham
If they are, then the subform/subdatasheet will show only those accounts where BOTH the account holder AND the joint holder are equal to the current person record. Of course, such an account should not exist.
If you want to have a subform to show all accounts where the current person is either the primary holder or the joint holder, then I can't think how you could do it with automatic master/child linking. You would need to set a filter on the subform in the Current event of the main form.
--
Graham
ASKER
I've got it, in my original post I was to establish a many- to -many relationship with just 2 tables. After a bit of research, I learned that Access does not directly allow such a case. I instead then crated a 3rd internm table (call it MM) with foreign keys from both PERSON and ACCOUNT. then I established a 1-to-many relationship from PERSON to MM and another 1-to-many relationship from ACOUNT to MM. This solved the need to do without the multiple realtionships in a table and got my subdatasheets working.
Thank you for all your help.
Thank you for all your help.
To add a second relationship between two tables, modify the existing one by double clicking on it then adding the new joining fields in the table that is displayed (underneath the current relationship).