You should remove the duplicated fields completely from two of the tables,
apart from the PatientID fields. For example you might have a Patients table
with fields PatientID, FirstName, LastName, DateOfBirth etc. You might have
another table PatientDiagnoses with fields PatientID, Dagnosis, DateDiagnosed
etc. You do not need the Patient's names or date of birth in the
PatientDiagnoses table as you can join the two tables on the PatientID fields
in a query, or in a form/subform as described below, to get the data from
both.
A fundamental principle of a relational database is that each 'fact' is
stored only once. This eliminates redundancy and possible errors which might
arise if the same 'fact' is entered differently in more than one table.
In the above example the relationship between Patients and PatientDiagnoses
is one-to-many as each patient might be diagnosed with more than one
condition. The normal way to enter such data would be to have a form, in
single form view, based on the Patients table and a subform within it, in
continuous form or datasheet view, based on the PatientDiagnoses table. The
subform would be linked to the parent form on PatientID, so when the parent
form is at a patient's record the subform would show his or her diagnoses.
You can have more than one subform in a parent form if necessary, each based
on different tables related to the main patients table.
Ken Sheridan
Stafford, England
