Related Record Required

[ungratefulninja]

I am designing a database with three tables.



The main table - ALL - has records for all participants in a study. The two subtables tables - Employed and Not_Working - should have records only for employed and not working participants respectively, and thus are each a subset of the records in the main table.

When I have designed databases along these lines before, I was able to create relationships between the All table and the two partial-tables by choosing the join type "Include ALL records from 'Emp' and only those records from 'All' where the joined fields are equal," and this worked perfectly well. I could enter data into the All table even where there was no related record in the Emp table -- a related record was only created after data was entered in the Emp table.

This time, however, I keep getting the error message that a related record is required -- specifically in the Emp table, though there are two subtables with relationships to the main table. Have I chosen the wrong join type? I have also tried chancing the join type and it doesn't seem to help.

Frustratingly, I can create records in either subtable without a record in the main table (which shouldn't be allowed), so obviously I have something set backwards here.

[June7]

Is the option Enforce Referential Integrity checked for the join in Relationships builder?

Want to provide db for analysis? Follow instructions at bottom of my post.

[ungratefulninja]

Is the option Enforce Referential Integrity checked for the join in Relationships builder?

Yes, I have checked the Enforce Referential Integrity option.

I'm sorry, I can't provide the database as it is confidential

[ungratefulninja]

I just noticed - of course, you said to remove confidential data. I'll try that for posting.

[ungratefulninja]

T3_Tel_TABLES_testing.mdb

Here is the database in question.

[June7]

If Enforce Ref Int is not checked then records can be entered anywhere.

I deleted the joins and recreated by dragging from the All table to each of the partial tables and select Enforce Ref Int. Now records are required in the All table before records in partial table allowed. Drag in the other direction and the reverse is established.

You are linking these tables on primary keys. This is a one-to-one relationship and structure is essentially a big flat file and your data does not appear to be normalized. The proliferation of similar named fields is an indicator of that. Will every field have value for every record?

[ungratefulninja]

I will try that solution and report back, thank you.

Yes, every record does have a value in every field in the all table (if only a code that represents "no entry"). It is a scientific study; we are required to record both null results and also need to export our data to statistical analysis software that requires one flat table for everything. The names are just codes for the questions (eg 21a, 21b, 21c). We are only using access to create user-friendly data-entry forms for our students.

[ungratefulninja]

That worked - thank you.