Solved Cannot add record because a related record is needed in another table - unable to find cause

[jdraw]

Has this really been solved?

 

[GaP42]

The technical issue causing the message to appear has been solved: removal of referential integrity on a shared FK that points to different records in tables depending on the type (see #7), but that led to a discussion of the appropriate schema underlying the issue. That could be a different thread.

 

[jdraw]

GaP42,
If you decide to make a new thread specific to structure/restructure, then I recommend you provide as many business rules/facts as possible and provide some definitions/descriptions of the entities. We don't know your business and environment nearly as well as you. You are intimately familiar with your current structure and the rationale involved. Sometimes we get so close to the "issue/opportunity" that we assume some things or overlook a few facts. Sometimes just describing things to an uninformed 3rd party highlights some new thinking or key revisions.

 

[cheekybuddha]

This seems wrong in the first place.

Employee/Contractor/Volunteer seems either to be a person type - if they can only ever be on type, then a single field in the person table, or a property of their relationship to an organisation

But more flexibly you would have a junction table between persons and organisation where you record start date/end date and person type.

This allows for someone to go from being a contractor to an employee at a later stage, or concurrently be an employee in one organisation whilst a volunteer in another ...

 

[The_Doc_Man]

The technical issue causing the message to appear has been solved: removal of referential integrity on a shared FK that points to different records in tables depending on the type (see #7), but that led to a discussion of the appropriate schema underlying the issue. That could be a different thread.

I want to say this respectfully, because I know you have things to do. However, I have to offer the opinion that removing RI wasn't a solution - it was a "baling wire and spit" type of fix to hold something together until the next disaster. Granted, that could indeed be a distinct thread.

However, as long as you make a trichotomy of people types (ignoring or glossing over that they are ALL people who can do the same kinds of activities), you will encounter massive issues SOMEWHERE down the road because of the ambiguous nature of that relationship. In particular, a JOIN query that involves the three different people types as the middle table of a three-layer JOIN would almost certainly become non-updateable, RI or no RI, because of a three-way ambiguity of relationships leading through (but not ending in) your three parallel tables.

 

[Pat Hartman]

You have circular relationships. Anything related to a persion is by definition related to the organization to which the person belongs.

If you want everything to be related to an agreement/contract, create a new Person record each time the person rejoins the organization.

Why does group have two relationships with the Person table?
Can a person really be a member of two organizations at the same time?

 

[isladogs]

See if my 3 part series of articles on relationships & referential integrity helps your understanding
The first article is :

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

www.isladogs.co.uk