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

[GaP42]

I have been struggling to find the cause of this message appearing when entering a new record on a subform, and would appreciate suggestions of how to locate why this appears. The context is:
I have 3 similar forms to record some details of the work force: as Employees, as Contractors and as Volunteers. A subform, specific to each type, is on each of these parent forms to allow the activities they have performed, or are assigned, can be viewed, edited added to. Each follows a similar format: the queries for the subforms are very similar (and based on the same tables, filtering by the type of worker). The entry of activities against a Volunteer record can be performed with no problem. With the Employee form, when an activity record is added, if there are existing records, the activity subform works fine. However when there are no activity records for an Employee I get the message: You cannot add or change a record because a related record is required in the table 'tblVolunteers'. The query for the Employee activities subform does not reference the Volunteers table. I can add a record through the datasheet view of the query used for activities for Employees. The parent form does not reference the Volunteers table, and the Parent/child link between them uses the EmpID to the HRID for Activities. I cannot see why it should be throwing the message. Appreciate any suggestions of what I should inspect/ recheck.

 

[theDBguy]

Are there any relationship links between employees and volunteers?

 

[GaP42]

Are there any relationship links between employees and volunteers?

No ... the Volunteers, Employees and Contractors have a relationship to HR Activities, but not directly to one another.

 

[theDBguy]

No ... the Volunteers, Employees and Contractors have a relationship to HR Activities, but not directly to one another.

Can you post a sample db with test data?

 

[The_Doc_Man]

The basic explanation is that there must be a dependency between the two tables in the form of a formal relationship. If the independent (a.k.a. parent side) of that relationship isn't filled in for a given ID value, no dependent (a.k.a. child side) records can be entered. This is a form of relational integrity and cannot be ignored when it is declared. I will address TWO issues that your post reveals.

1. You have a table for Employess vs. Activities. If there is a formal relationship between them, be sure that the "arrow" points the right way, such that your employee is the independent or parent of the relationship. Look for enforcement of Relation Integrity. In the form in question, look at the parent/child links since you have a main/sub form situation. Access will know that it needs to save the employee record first before entering any activity data.

2. You have named three tables that differ by Employee vs Contractor vs Volunteer. They all sound like Persons to me and should perhaps be collapsed into one table with a field that says what they are. Then if you need separate lists, you use a query to pick the particular "flavor" out of that one table. This is a normalization issue that probably will haunt you one day. This might SOUND like a nit-pick, but it is a very annoying nit that could easily reach out to bite you in the ... derriere.

 

[GaP42]

Can you post a sample db with test data?

Thanks - that will take some time to get together and check .. I will get to it if I cannot resolve following suggestions / discussion with Doc_Man.

 

[GaP42]

The basic explanation is that there must be a dependency between the two tables in the form of a formal relationship. If the independent (a.k.a. parent side) of that relationship isn't filled in for a given ID value, no dependent (a.k.a. child side) records can be entered. This is a form of relational integrity and cannot be ignored when it is declared. I will address TWO issues that your post reveals.

1. You have a table for Employess vs. Activities. If there is a formal relationship between them, be sure that the "arrow" points the right way, such that your employee is the independent or parent of the relationship. Look for enforcement of Relation Integrity. In the form in question, look at the parent/child links since you have a main/sub form situation. Access will know that it needs to save the employee record first before entering any activity data.

2. You have named three tables that differ by Employee vs Contractor vs Volunteer. They all sound like Persons to me and should perhaps be collapsed into one table with a field that says what they are. Then if you need separate lists, you use a query to pick the particular "flavor" out of that one table. This is a normalization issue that probably will haunt you one day. This might SOUND like a nit-pick, but it is a very annoying nit that could easily reach out to bite you in the ... derriere.

Thank you - re issue 2 first. The tables for Employee, Contractor and Volunteer only hold info about those roles that the Person may have, and indeed there is a common Persons table that hold the personal details. Each type of worker may undertake/ be assigned an activity. The query used for Volunteers - Parent Form (the Engagement) and Child (Activity) is as attached. For Employees and Contractors, the Volunteers table is substituted by EmpRegister and Contactors (with some variation in the attributes). The HRTypeID is 1 for Employees, 2 for Contractors and 3 for Volunteers. I think for the purpose that this structure properly normalised.

Re issue 1: I have checked and rechecked the relationship as defined in the queries for each of the worker types - as shown above - these are consistent. However I just found a possible discrepancy in the relationships between HR Activities and the "worker" tables in the back-end db relationships (referential integrity is not enforced for Employee and Contractor, but is for Volunteers) - as viewed from the FE. I will investigate the BE directly and provide an update.

UPDATE: Yes - that was it - but note - it required the removal of referential integrity between Activity and Volunteers as the HRID in Activity shares a relationship to each of the types of workers. Integrity must be maintained by appropriate allocation of the HRTypeID and HRID to Activity records.

Thank you

 

[Pat Hartman]

Relationships are not defined by queries. What you see in queries are joins. A join is simply a temporary instruction on how two tables/queries should be connected for the purpose of the query. A relationship is a permanent connection between two tables and it can and usually does, enforce referential integrity.

You need to be looking at your relationship window in the BE database. Make sure that all relationships are shown.

 

[GaP42]

Relationships are not defined by queries. What you see in queries are joins. A join is simply a temporary instruction on how two tables/queries should be connected for the purpose of the query. A relationship is a permanent connection between two tables and it can and usually does, enforce referential integrity.

You need to be looking at your relationship window in the BE database. Make sure that all relationships are shown.

Thanks Pat - yes, I saw the issue looking at the (not maintained) relationships view from the FE, but went to the BE relationships view to make the correction (which does have all the wanted relationships defined).

 

[Pat Hartman]

I don't ever draw the relationships in the FE. All that does is to confuse people like it confused you. I would remove all relationships from the FE since nothing matters but what is defined in the BE. Only the Database that physically holds a table can define RI or enforce it.

 

[GaP42]

I don't ever draw the relationships in the FE. All that does is to confuse people like it confused you. I would remove all relationships from the FE since nothing matters but what is defined in the BE. Only the Database that physically holds a table can define RI or enforce it.

The relationships weren't drawn / stored in the FE - I just went to the Relationships view to add the tables (temporarily) to see how their relationships were defined in the BE - I did not need to draw any new relationships in the FE .. albiet confused by the message I was seeing - thanks

 

[The_Doc_Man]

Thank you - re issue 2 first. The tables for Employee, Contractor and Volunteer only hold info about those roles that the Person may have, and indeed there is a common Persons table that hold the personal details. Each type of worker may undertake/ be assigned an activity.

From the sound of this, if there IS a relationship between activities and something else, it should be the person table. By adding an intermediate table (or actually, set of tables) between persons and activities, you triple the number of places where you can have a confused relationship.

In particular, you have the potential for having a record in Activities depend on any one of three different tables if the Employee, Volunteer, and Contractor each link to the same activity table. This is another normalization issue.

 

[GaP42]

Thanks Doc, but really, the persons table holds data about people that are not workers as well, that do not undertake HRactivities on behalf of the charity (eg donors/ participants in courses / events ...). The activities are HR activities - as the table name suggested - it is not about any activity. There are some interesting design features: Activities - described by a "type" and date start / end and Persons are joined by tables holding the ID for the person and the Activity - at least in terms of the VolID for Volunteers, the ContractorID and EmpID - these Volunteer, Contractor and Employee tables are, in this view, join tables with the particular attributes relating to those types of workers. The HRActivity table itself holds mostly FKeys - and so also represents a many to many join of workers to events.

The design allows a worker to be engaged for a time, leave and later return so a new record of engagement can be created (as the same or different type of worker), so a work history can be compiled for a person across all their work engagements and the HRactivities undertaken in those engagements (and where applicable the events those activities related to) .. there does not seem to be a problem with how it is set up - no data redundancy, data is maintained in one place. Many people can have a work record which shows they did the same type of activity, even at the same event.

My original choice in design was about whether to have separate attributes for the ID for the Volunteers, for Contractors and Employees. I chose to use a common ID (HRID) with HRIDType, as it meant, that should a different type of Worker label or type be needed this could be handled without change to the HRActivities table, as opposed to creating another column for the new type (altho no small amount of dev work would be needed to implement in the app, either way). That of course means that the relationships to the common ID (HRID) cannot enforce referential integrity. But given that this table does not need to cascade updates/ deletes this does not present a significant problem.

(btw - working on participants at events: this is a Persons - Participants - Events, so may have a volunteer as a worker doing workshop facilitation at the event and separately participating at the same event)

So Doc_Man you would have made the tables Volunteers, and Employees and Contactors one table? I was tossing it up too but eventually went this way to make it more apparent what each held, and not make the attributes of each more abstract or label them with prefices relating to the type of worker

 

[The_Doc_Man]

In the U.S. Navy Reserve Personnel database, we had a PERS table and then several ADDL_INFO_xxxx tables. Everything was linked to PERS, not to the tables that held _ENL data (enlisted) or _OFC data (officers) or _WRO (warrant officers), each of which had their differences.

My concern, amplified by your revelation of the table structure, is that to build proper relationships between an Activity and a person, you have to build a link between a person-related table's PK and the person-related FK of the Activity. But if your EmpID, ContractorID, and VolID are each formed via autonumber, then you have three tables with potentially overlapping IDs trying to link to your Activity table. This activity is performed by a person who HAPPENS to be an employee, contractor, or volunteer. Structurally, your tables enforce a separation yet your previous description suggests that you use the same Activity table for each person.

If you linked according to PersonID (which implies that PersonID is the PK of your persons table), there is no ambiguity. If you linked with the three sub-class tables, from the activity you would not know who did what - because the sub-class tables could theoretically have overlapping IDs for their PK values. AND the activity dependency is based on an ID that doesn't properly identify which kind of person you are using.

Look, I'm not trying to busy your chops here. I'm just trying to warn you that there is a logical and structural issue in that which you have described to us. We ALL hate having to retrofit things. I get that. But there is a danger of confusion here. And Access has a predictable response to cases when it gets confused. It stops working.

 

[Pat Hartman]

I'm confused. If you have a person table. THAT is the table that is related to all the activities. The employee, contractor, volunteer tables are not related to anything except for the Person table. That schema works fine but all the common fields belong in the Persons table. Fields like name and start/end dates as well as contact information and possible pay rates.

 

[GaP42]

Thanks Doc_Man and Pat - I have a bit more clarity around the issue now and will explore it, despite the work needed - it is after all in a development stage. My current view is that while I basically re-align the worker tables to sub-tables of person, I will also need to look carefully at the attributes that belong there. Persons (as workers) can have multiple Engagements (periods of "employment") in which multiple Activities/Services are performed (these may then relate to events) - and it is in the Engagement table that the start/end, applicable pay rate, contract info, etc belongs.
And in the process, for an Event multiple people can participate, and so a participation table will be needed to resolve the m:n

 

[Pat Hartman]

If when a person is rehired, you want him to have his original PersonID (and I think he should), then the dates go into the individual child tables. You then have to have a way to identify the active record. It would be the one with the null end date. You would control that by not allowing a new child record if any existing record for the same PersonID had a null end date.

 

[GaP42]

Hi Pat. Each time a person is re-hired (re-engaged) in whatever capacity, a start date for the engagement is needed, an agreement/ contract is extended/renewed. The structure Person --> Engagement --> Activity supports the business rule that you cannot undertake an activity unless you have an agreement (and more strictly on or after the date of commencement of the engagement, and as you point to, not having multiple simultaneous engagements of the same type - although that may be pushed in some unusual circumstances of being part-time or casual in two roles). Further, persons should not undertake activities for the organisation without some formal record of engagement (to cover legal, insurance issues). It becomes a much more difficult task to align a work history of activities with engagements if there is no relationship between them (altho granted the discipline of ensuring activity records are fully maintained is a likely issue). Reviewing the attributes on the Contractor, Employee and Volunteer tables - they were about defining the engagement of the person as a contractor (ContractorEngagementRegister), Employee (EmployeeEngagementRegister) and Volunteers (VolunteerRegister). The date start / end, payrate etc are attributes of the engagement, not the Person. Of course the Person ID is retained (indeed necessary as a FK) in each of the engagement records, allowing reporting of all engagements of any type undertaken by a person (and has been implemented in the app). However that ID alone is not sufficient to specifically locate a given engagement record, and so points to the need for dedicated tables to hold engagement records. That also leads us back to the relationship to the Activity table - where Engagements could be represented in one table or 3, has an impact upon the implementation of referential integrity between these tables - which was the cause of the original post.

 

[Pat Hartman]

It's your schema. You can make it not normalized if you want to. It will be a nightmare to manage. In a properly normalized schema, only the PersonID would ever be related to any activity. The three types tables are only ever related to the Person table. It is the person doing the job, not the engagement. If the engagement is relevant, you need to use dates to get to the relevant engagement record.

 

[GaP42]

At the risk of further discussion, I cannot leave it at this point. I have clumsily drawn 2 high level models to compare what I think are two valid positions:
The first shows Person and Activity separated by Engagement, of which there are three types relating to people and another where an organisation may be contracted. This arrangement enforces the business rule that any specific activity must be performed by one of those 4 classes (although any number may perform the same type of activity). The person ID provides the link across those 3 classes to enable a work history to be generated for any one person engaged at any time.

The second provides a direct link between Person and Activity. Agreements/contracts are not directly associated with activity. The business rule is not enforced in the data model however that is not to say it cannot be enforced through code. The same classes exist in both. The employment/engagement agreement provides a capability generate the work history to the extent of the engagement but not to the specific activities performed during that agreement except by matching overlapping dates. Again the specific AgreementPK has to relate to only one of the classes available, or create individual child tables. [And before going to far - at this stage, I only see Organisation Agreements having a direct relationship to Events]

So then there is the key, the whole key and nothing but the key.