Access 2007, cannot enforce referential integrity

dev45
dev45 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Add a second "copy" of PERSON into the relationships window (It will be named "PERSON_1" but don't worry about that).

Then add the second relationship by dragging the PK from PERSON_1 to Joint Holder.
--
Graham Mandeno - Access MVP
ioanePlanning & Analytics Manager

Commented:
Sounds like you are trying to create another relationship.

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).
ioanePlanning & Analytics Manager

Commented:
Sorry, when I say double click on the existing relationship, I mean the joining line between the two tables.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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
ioanePlanning & Analytics Manager

Commented:
[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.

Author

Commented:
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

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial