Entering data in a field enters the same data in other forms/field

[John]

I have a database with four seperate tables. Patient Demographics, Surgical
Information, Follow up and Patient Satisfaction. I used the patients medical
record number as the primary key in each of the tables since it stays the
same for the life of the patient and is unique for every patient. I joined
the tables with a one to one relationship and enforced referential integrity
and cascade update. I would assume that once I add the medical record number
into the Patient Demographics table for record number one it should
automaticly update that medical record number in all of the other tables
where the medical record number field is for record number one. It does not
update. I need to type it in for each table. What am I doing wrong?

 

[Jeff Boyce]

John

If you "joined the tables with a one-to-one relationship {with the Patient
Record Number as primary key)", then each patient can only have one
[Surgical Information] record, and only one [Follow Up] record, and only one
[Patient Satisfaction] record.

If this accurately reflects your situation (and I somehow doubt each patient
is only seen once...), then why bother with 4 tables?

Using a one-to-one table relationship is more commonly used when sub-typing,
which is not what you've described.

And why would you think that Access "knows" it is supposed to automatically
add records in the other tables when you add a new patient record?

Please describe your situation in a bit more specific detail ... it all
starts with the data (not the structure!).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have a database with four seperate tables. Patient Demographics, Surgical
Information, Follow up and Patient Satisfaction. I used the patients medical
record number as the primary key in each of the tables since it stays the
same for the life of the patient and is unique for every patient. I joined
the tables with a one to one relationship and enforced referential integrity
and cascade update. I would assume that once I add the medical record number
into the Patient Demographics table for record number one it should
automaticly update that medical record number in all of the other tables
where the medical record number field is for record number one. It does not
update. I need to type it in for each table. What am I doing wrong?

Quite a few things.

As noted elsethread, a one to one relationship means that each table can have
either zero or one records for each patient... never more. That's unusual and
probably wrong.

More fundamentally, you're misunderstanding how relationships work. A
relationship PREVENTS invalid data from being added to a table (e.g. for a
nonexistant MedicalRecordNumber). It does not (and should not) automagically
create empty, useless "placeholder" records.

Normally one would have one to MANY relationships between the Patient
Demographics table and the related tables, and use a Form (based on
Demographics) with Subforms (for the related tables), using Record Number as
the master/child link field. Doing so will cause data entered on a subform to
inherit the currently displayed record number - at the moment that it's
needed, when you have data for the child table, not before.