If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Entering data in a field enters the same data in other forms/field
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? |
#2
|
|||
|
|||
Entering data in a field enters the same data in other forms/field
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" wrote in message ... 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? |
#3
|
|||
|
|||
Entering data in a field enters the same data in other forms/field
On Wed, 26 Aug 2009 13:01:01 -0700, John
wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|