regarding main and sub-form relationships

[Guest]

the ingredients of my situation consist in three tables

a) Audit Reports (parent): PK = "IRB Number"

b) Evaluations: PK = "IRB Number" and "Date of Audit"

c) Patients: PK = "IRB Number", "MedRecNum" and "Date of Audit" (where
medrecnum means patient's medical record number

my relationships have a one to many between 'a' and 'b' linked via "IRB
Number" and a one to many between 'a' and 'c' linked via "IRB Number" as well.

i have created a form called "Audit Reports" having the source table 'a'
onto which i slapped two sub-forms called "Evaluations" and "Patients" having
the source tables 'b' and 'c' respectively.

does this seem 'OK'?

it doesn't seem to be working quite yet---by which i mean when i add data
into all three forms, close it and then re-enter, the 'Audit Reports' form
opens in add record mode and won't let me see what's already been entered?
this seems definitely screwy and i've spent too much time going around in
circles trying to puzzle it out so i'm hoping the answer's 'obvious' to
someone willing to share their expertise w/ me.

IRB number refers to a medical experiment which can have lots of patients on
it (each one being uniqeuly identified by a hospital med record number; sort
of like the social security number) whose date are assembled into folders
(having their med rec number) over time (possibly lasting years). during the
life of the experiment auditors will pop up from time to time and randomly
select med record numbers (folders) to pour through. when they do corresponds
with the Date of Audit.

so records in the Audit Report table uniquely identify each medical
experiment (IRB number).

records in the Evaluation table uniquely identify the Dates of Audit
performed for each IRB number.

records in the Patients table uniquely identify each patient (med record
number) whose folder was audited on that particular Date of Audit in that
particular med experiment (IRB number).


-ted

 

[Pat Hartman]

The primary key for the patients table doesn't look right. The PK for
patients should be only the MedRecNum. Since over time, the same patient
might participate in more than one IRB, you need a relation table that
contains just MedRecNum and IRB as the multi-field primary key. Date of
Audit does not belong in this table nor in the patients table.

When working with 1-many and many-many relationships, you should use main
and subforms. The master/child links need to be properly set and Access
will then populate the foreign keys in the subform records.

 

[Guest]

hi, so you're saying this is one of those instances where we have a many-many
relationship? this marks something of a turningpoint for this writer, then...

could you be a bit more basic, pat. because of the above description. i have
worked with 1-many relationships in many instances in my mdbs and in the
context of parent/child linked form/sub-form. here things are different it
seems.

to get this to work at the interface level, i thought the user would like to
see a single screen per IRB. atop the screen there would be the data entry
fields/controls which addressed the identification of "static" information
(the IRB Number and sundry other controls that apply to the description of
the medical study and remain constant). appearing below, i envisaged a
datasheet sub-form view of a table having a link with the parent via the IRB
number (with PK comprising IRB and Date of Audit) which would enable user to
record information relevant to the auditors evaluation of an audit that
happened at the Dte of Aud. just below the "Evaluations" datasheet, i
envisaged the 2nd and final sub-form (datasheet view of a table having PKs
consisting of IRB, Dte of Aud and Medrecnum) which would link with the parent
via IRB number and would describe any issues arising at the patient level on
that audit (e.g. say that on an audit conducted on a certain date for a
certain IRB number a certain patient's informed consenst form could not be
identified).

my issue is bridging the above interface with the points you're raising. can
you fill in the voids?

best,

-ted

 

[Pat Hartman]

The patient table would normally contain some information about the patient.
That information has no relationship with his participation in a study.
That is why I suggested removing the other two fields from the patient
table's primary key. You wouldn't want different names for the patient or
different addresses, etc. If a patient can participate in one and only one
study EVER, then you have a 1-many relationship and you can store IRB as a
foreign key in the patient table. However, if the patient may at some time
in the future, participate in a different IRB, then there is a many-many
relationship and you would need a relation table to accommodate that
structure.

 

[Guest]

hi pat,

i'm sorry about any confusion arising from the description i put together.
based on your first sentence you've gathered that the patient table was
irrelevant to the mdb. quite the opposite (again apologies)....the patient
table contains information regarding the results of the audit of the
patient's 'folder' (containing his lab results, informed consents, etc. etc.
arising from his being enrolled in the experiment; e.g. if his consent form
could not be located in the folder, then that control would get an
'Unacceptable' as opposed to an 'Ok' result) so it's quite germaine.

the patient (as in the patient's "MedRecNum", medical record number) can
appear in more than one study (e.g. if a particular drug under investigation
proves to be too toxic and requires the patient's dropping out of a study,
the investigational team may invite the patient to enroll in a future study
for which he may be a good candidate). if that is the case, the same
MedRecNum will appear in more than one study (and apply to the same person).

as best as i've been able to understand from the folks gathering this data
and wishing to have somewhere like this mdb to archive it, the mdb will
enable the generation of recurring reports which describe a) the details
surrounding the audit of the patients' folders for a particular study at a
particular Date of Audit (this i saw as being captured by the 'Patients'
table) and b) the higher-level, 'overall' results of the audit for that study
on that Date of Audit (e.g. PharmacyReview was 'OK', etc etc) which would be
captured by what i called the 'Evaluations' table

always assuming your interest, how do you see this sorting itself out.
leaving aside the issue of the non PK controls in the tables, what tables
would you see this requiring, what would be the PKs in each and how would
they be interrelated in order to enable the recording and reporting of those
data?

best,

-ted