regarding relationships and sub-forms

[Ted]

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.

-ted

 

[Tim Ferguson]

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.

Apart from the fact that I hate the field names (spaces and other
questionable characters are legal in Access but _will_ bite you one day),
I don't think this model is quite right. The relationships you have set
up will allow a Patient to have a ([IRBNumber], [Date of Audit]) that is
not a valid Evaluation. If this is what you mean to have, then fine, but
it seems odd.

I must confess that I would have expected one Patient to be identified by
a single MedRecNum. It seems that you can have several Patients sharing a
MedRecNum as long as they have different Dates Of Audit and IRB Numbers.
Is this what you meant?

If Patients are not actually people at all, then perhaps a better name
would be PatientsEvaluatedAsPartOf thus indicating that it is a junction
table between Patients (*MedRecNum) and Evaluations (*IRBNumber,
*DateOfAudit).

And the relationships should be
("Audit report contains Evaluations")
Contains = Evaluations.IRBNumber references AuditReports.IRBNumber

and
("Evaluations consist of PatientsEvaluatedAsPartOf")
ConsistOf = PatientsEvaluatedAsPartOf(IRBNumber, DateOfAudit)
references Evaluations(IRBNumber, DateOfAudit)

and there is _no_ relationship between PatientsEvaluatedAsPartOf and
AuditReports because it's enforced anyway via the other two.

If this sounds like nit-picking, it's because db design is all about
semantics and getting the language wrong is critical when you come to
review it later, or hand it over to someone else.

Hope it helps


Tim F

 

[Ted]

i'm going to respond by interspersing my replies below, tim.
but by way of prologue, let me expand on my initial precis.

let's say that each IRB Number uniquely identifies a medical experiment into
which different numbers of patients (which are uniquely identified by their
hospital med rec number --- med rec number could be treated as you would soc
sec number. the same patient were (s)he enrolled in more than one experiment
would have the same med rec number on each).

each record in he Audit Reports table uniquely identifies each med experiment.

patient records are assembled into folders for each patient (med rec number)
in a medical experiment (i.e. per IRB number).

the studies can last for months and/or even years.

during the tenure of a medical experiment (IRB number), auditors can and do
on (more than one) occasion arrive and ask to have patients' files pulled
(usually this is done at random). the audit date is the date when the audit
is perfomed.

records in the Evaluations table uniquely identify the dates when audits are
performed for any given IRB number.

during each audit (audit date) some sample of patient folders (med rec
numbers) gets pulled and poured over by the auditor(s).

records in the Patients table are intended to uniquely capture which
patients (med record numbers) were audited during the audit date (which we
recorded in the Evaluations) table.

this seemed fairly straightforward to me as i was designing it and thought i
translated the interdepencies via the relationships maps.

the main form is linked to each of the sub-forms via the IRB number.

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.

Apart from the fact that I hate the field names (spaces and other
questionable characters are legal in Access but _will_ bite you one day),
I don't think this model is quite right. The relationships you have set
up will allow a Patient to have a ([IRBNumber], [Date of Audit]) that is
not a valid Evaluation. If this is what you mean to have, then fine, but
it seems odd.

how do you mean allow a patient to have an irb number,date of audit that is
not a valid evaluation? assuming you are correct what would the correct model
be?

I must confess that I would have expected one Patient to be identified by
a single MedRecNum. It seems that you can have several Patients sharing a
MedRecNum as long as they have different Dates Of Audit and IRB Numbers.
Is this what you meant?

i think i explain this above....but, a med record number could be likened to
your SSN. your folder could be audited more than once (by random chance) w/in
the same med experiment (IRB number) albeit not on the same audit date. make
sense?

If Patients are not actually people at all, then perhaps a better name
would be PatientsEvaluatedAsPartOf thus indicating that it is a junction
table between Patients (*MedRecNum) and Evaluations (*IRBNumber,
*DateOfAudit).

patients (actually their folders) does refer to human beings.

And the relationships should be
("Audit report contains Evaluations")
Contains = Evaluations.IRBNumber references AuditReports.IRBNumber

and
("Evaluations consist of PatientsEvaluatedAsPartOf")
ConsistOf = PatientsEvaluatedAsPartOf(IRBNumber, DateOfAudit)
references Evaluations(IRBNumber, DateOfAudit)

and there is _no_ relationship between PatientsEvaluatedAsPartOf and
AuditReports because it's enforced anyway via the other two.

If this sounds like nit-picking, it's because db design is all about
semantics and getting the language wrong is critical when you come to
review it later, or hand it over to someone else.

Hope it helps


Tim F

where do we go from here?

best,

-ted

 

[Tim Ferguson]

Hello Ted

let's say that each IRB Number uniquely identifies a medical
experiment into which different numbers of patients (which are
uniquely identified by their hospital med rec number --- med rec
number could be treated as you would soc sec number. the same patient
were (s)he enrolled in more than one experiment would have the same
med rec number on each).

Okay, there are three entities right here: MedicalExperiments, Patients,
and Enrollments...

patient records are assembled into folders for each patient (med rec
number) in a medical experiment (i.e. per IRB number).

When one patient is enrolled in more than one experiment, does he have
several folders (i.e. several MedRecNums) or one shared between the
experimentors?

during the tenure of a medical experiment (IRB number), auditors can
and do on (more than one) occasion arrive and ask to have patients'
files pulled (usually this is done at random). the audit date is the
date when the audit is perfomed.

Here are (at least) two more entities: Audits and RecordPullings. Each
Audit record is related to a single MedicalExperiment; each RecordPulling
is related to a single Audit and to a single PatientFolder or Patient.

records in the Patients table are intended to uniquely capture which
patients (med record numbers) were audited during the audit date
(which we recorded in the Evaluations) table.

Which is why I suggested that Patients was not a suitable name: the
records are actually
AuditsOfPatientsInvolvedInAnExperimentOnAParticularDate if you see what I
mean. It made more sense when you described them above in terms of
RecordPullings!

this seemed fairly straightforward to me as i was designing it and
thought i translated the interdepencies via the relationships maps.

It probably is, but you have to identify all the entities and understand
the power of calling them the right things

the main form is linked to each of the sub-forms via the IRB number.

PLEASE don't even think about forms or UI considerations before you've
got the tables and fields down pat.

The relationships you
have set up will allow a Patient to have a ([IRBNumber], [Date of
Audit]) that is not a valid Evaluation. If this is what you mean to
have, then fine, but it seems odd.

how do you mean allow a patient to have an irb number,date of audit
that is not a valid evaluation?

Because you did not specify a relationship between that combination and
the Evaluations table.

assuming you are correct what would the correct model be?

I don't think I was correct about the model. Your description above made
much more sense, and we are getting closer to a correct design.

where do we go from here?

Just to recap: what I _think_ is going on is something like:-

MedicalExperiments --< Enrolments >-- Patients

MedicalExperiments --< Audits --< RecordPullings >- Patients

Of course; it's your setup, and I can't see everything you need, but this
seems to fit with what you described above.

Hope it helps


Tim F

 

[Ted]

hi tim, sorry about all the follow-up postings, but could you flesh out what
you meant by "enrollments" in your statement about "there are three entities
here...". i would've seen patients under IRB number as a defacto 'enrollment'
.. also, i don't think the part about "here are (at least) two more entities:
Audits and Record pullings" is quite on target. meaning, during any given
Audit Date there are audits of several patients folders. perhaps we're
talking about the same thing but just in different terms?

Hello Ted

let's say that each IRB Number uniquely identifies a medical
experiment into which different numbers of patients (which are
uniquely identified by their hospital med rec number --- med rec
number could be treated as you would soc sec number. the same patient
were (s)he enrolled in more than one experiment would have the same
med rec number on each).

Okay, there are three entities right here: MedicalExperiments, Patients,
and Enrollments...

patient records are assembled into folders for each patient (med rec
number) in a medical experiment (i.e. per IRB number).

When one patient is enrolled in more than one experiment, does he have
several folders (i.e. several MedRecNums) or one shared between the
experimentors?

during the tenure of a medical experiment (IRB number), auditors can
and do on (more than one) occasion arrive and ask to have patients'
files pulled (usually this is done at random). the audit date is the
date when the audit is perfomed.

Here are (at least) two more entities: Audits and RecordPullings. Each
Audit record is related to a single MedicalExperiment; each RecordPulling
is related to a single Audit and to a single PatientFolder or Patient.

records in the Patients table are intended to uniquely capture which
patients (med record numbers) were audited during the audit date
(which we recorded in the Evaluations) table.

Which is why I suggested that Patients was not a suitable name: the
records are actually
AuditsOfPatientsInvolvedInAnExperimentOnAParticularDate if you see what I
mean. It made more sense when you described them above in terms of
RecordPullings!

this seemed fairly straightforward to me as i was designing it and
thought i translated the interdepencies via the relationships maps.

It probably is, but you have to identify all the entities and understand
the power of calling them the right things

the main form is linked to each of the sub-forms via the IRB number.

PLEASE don't even think about forms or UI considerations before you've
got the tables and fields down pat.

The relationships you
have set up will allow a Patient to have a ([IRBNumber], [Date of
Audit]) that is not a valid Evaluation. If this is what you mean to
have, then fine, but it seems odd.

how do you mean allow a patient to have an irb number,date of audit
that is not a valid evaluation?

Because you did not specify a relationship between that combination and
the Evaluations table.

assuming you are correct what would the correct model be?

I don't think I was correct about the model. Your description above made
much more sense, and we are getting closer to a correct design.

where do we go from here?

Just to recap: what I _think_ is going on is something like:-

MedicalExperiments --< Enrolments >-- Patients

MedicalExperiments --< Audits --< RecordPullings >- Patients

Of course; it's your setup, and I can't see everything you need, but this
seems to fit with what you described above.

Hope it helps


Tim F

 

[Ted]

hi tim,

in response to the point you're raising wrt whether the medrecnum is shared
amongst the experimentors, the answer is that it's shared. a person has only
ONE medrecnum irrespective of his/her enrollment in a clinical trial (if
you're in the US, it's like the social security number --- always the same
irrespective of your emplyer). hth.

fast forwarding to your closing paragraph, i'm not acquainted with the
visual symbology. can you decode this into english.

lastly, what's the bottom line wrt the where do we go from here question i'm
wrestling with, e.g do we need to re-engineer some tables.....???

-ted

Hello Ted

let's say that each IRB Number uniquely identifies a medical
experiment into which different numbers of patients (which are
uniquely identified by their hospital med rec number --- med rec
number could be treated as you would soc sec number. the same patient
were (s)he enrolled in more than one experiment would have the same
med rec number on each).

Okay, there are three entities right here: MedicalExperiments, Patients,
and Enrollments...

patient records are assembled into folders for each patient (med rec
number) in a medical experiment (i.e. per IRB number).

When one patient is enrolled in more than one experiment, does he have
several folders (i.e. several MedRecNums) or one shared between the
experimentors?

during the tenure of a medical experiment (IRB number), auditors can
and do on (more than one) occasion arrive and ask to have patients'
files pulled (usually this is done at random). the audit date is the
date when the audit is perfomed.

Here are (at least) two more entities: Audits and RecordPullings. Each
Audit record is related to a single MedicalExperiment; each RecordPulling
is related to a single Audit and to a single PatientFolder or Patient.

records in the Patients table are intended to uniquely capture which
patients (med record numbers) were audited during the audit date
(which we recorded in the Evaluations) table.

Which is why I suggested that Patients was not a suitable name: the
records are actually
AuditsOfPatientsInvolvedInAnExperimentOnAParticularDate if you see what I
mean. It made more sense when you described them above in terms of
RecordPullings!

this seemed fairly straightforward to me as i was designing it and
thought i translated the interdepencies via the relationships maps.

It probably is, but you have to identify all the entities and understand
the power of calling them the right things

the main form is linked to each of the sub-forms via the IRB number.

PLEASE don't even think about forms or UI considerations before you've
got the tables and fields down pat.

The relationships you
have set up will allow a Patient to have a ([IRBNumber], [Date of
Audit]) that is not a valid Evaluation. If this is what you mean to
have, then fine, but it seems odd.

how do you mean allow a patient to have an irb number,date of audit
that is not a valid evaluation?

Because you did not specify a relationship between that combination and
the Evaluations table.

assuming you are correct what would the correct model be?

I don't think I was correct about the model. Your description above made
much more sense, and we are getting closer to a correct design.

where do we go from here?

Just to recap: what I _think_ is going on is something like:-

MedicalExperiments --< Enrolments >-- Patients

MedicalExperiments --< Audits --< RecordPullings >- Patients

Of course; it's your setup, and I can't see everything you need, but this
seems to fit with what you described above.

Hope it helps


Tim F

 

[Ted]

ps to my reply of five minutes ago...

regarding..

When one patient is enrolled in more than one experiment, does he have
several folders (i.e. several MedRecNums) or one shared between the
experimentors?

there is always one medical record number as i explained, however the
folders themselves are specific to the medical experiment (IRB number). so
patien w/ medcrecnum 88888888 who was enrolled in IRB 1111 and 4444 would
have two folders (with the paperwork specific to each IRB study) and have
88888888 appear on the papers identifying him/her in each folder. i felt my
last email might not've been sufficiently clear on that point.

regarding what is beginning to get called 'record pullings', i hope this
means to you what it means to yours truly. patients' records are unique to
each IRB number. the esperiments are probably for two different
medical/pharmaceutical interventions. in real life a patient might not be
able to tolerate the experimental drug be studies in IRB 1111 and be
withdrawn from it. later on another study might come along for which the
patient may appear to be a good candidate, like 4444, and separate paperwork
will be begun and 'folderized' for that one.

each IRB can and will have different numbers of patients as well as have the
possibility that a) the same patient's folder will be pulled more than once
(randomly) for an audit in a medical study b) the same patient can occur in
more than one IRB/study (and have a completely different folder -- because
each study has it's own data gathering requirements).

in case i haven't touched on the point of this database, there is an
interest in monitoring a) the outcome, overall, at each Audit Date for each
IRB number and b) the outcomes surrounding the patient folders audited on
that date in that IRB (e.g. which patients consent forms were not acceptable,
etc. etc.; these latter items would be captured in the patient specific table.

Hello Ted

let's say that each IRB Number uniquely identifies a medical
experiment into which different numbers of patients (which are
uniquely identified by their hospital med rec number --- med rec
number could be treated as you would soc sec number. the same patient
were (s)he enrolled in more than one experiment would have the same
med rec number on each).

Okay, there are three entities right here: MedicalExperiments, Patients,
and Enrollments...

patient records are assembled into folders for each patient (med rec
number) in a medical experiment (i.e. per IRB number).

When one patient is enrolled in more than one experiment, does he have
several folders (i.e. several MedRecNums) or one shared between the
experimentors?

during the tenure of a medical experiment (IRB number), auditors can
and do on (more than one) occasion arrive and ask to have patients'
files pulled (usually this is done at random). the audit date is the
date when the audit is perfomed.

Here are (at least) two more entities: Audits and RecordPullings. Each
Audit record is related to a single MedicalExperiment; each RecordPulling
is related to a single Audit and to a single PatientFolder or Patient.

records in the Patients table are intended to uniquely capture which
patients (med record numbers) were audited during the audit date
(which we recorded in the Evaluations) table.

Which is why I suggested that Patients was not a suitable name: the
records are actually
AuditsOfPatientsInvolvedInAnExperimentOnAParticularDate if you see what I
mean. It made more sense when you described them above in terms of
RecordPullings!

this seemed fairly straightforward to me as i was designing it and
thought i translated the interdepencies via the relationships maps.

It probably is, but you have to identify all the entities and understand
the power of calling them the right things

the main form is linked to each of the sub-forms via the IRB number.

PLEASE don't even think about forms or UI considerations before you've
got the tables and fields down pat.

The relationships you
have set up will allow a Patient to have a ([IRBNumber], [Date of
Audit]) that is not a valid Evaluation. If this is what you mean to
have, then fine, but it seems odd.

how do you mean allow a patient to have an irb number,date of audit
that is not a valid evaluation?

Because you did not specify a relationship between that combination and
the Evaluations table.

assuming you are correct what would the correct model be?

I don't think I was correct about the model. Your description above made
much more sense, and we are getting closer to a correct design.

where do we go from here?

Just to recap: what I _think_ is going on is something like:-

MedicalExperiments --< Enrolments >-- Patients

MedicalExperiments --< Audits --< RecordPullings >- Patients

Of course; it's your setup, and I can't see everything you need, but this
seems to fit with what you described above.

Hope it helps


Tim F

 

[Tim Ferguson]

Hello Ted

I'll try to pick up answers in each of the three postings in this
message.

there is always one medical record number as i explained, however the
folders themselves are specific to the medical experiment (IRB
number). so patien w/ medcrecnum 88888888 who was enrolled in IRB 1111
and 4444 would have two folders

.... which is to say he has been enrolled twice and therefore has two
Enrolment records. As a matter of language, these records are embodied by
real-world folders so you could call them Folders. In construction terms,
you need the table to maintain the many-to-many relationship between
Patients and MedicalExperiments.

patients' records are unique to each IRB number.

No they're not: they are unique to each Patient. There are lots of
patients' Folders attached to each IRB Number (MedicalExperiment), and
there is one Patient record attached to each Folder (but potentially
several folders for one patient).

each IRB can and will have different numbers of patients

Exactly

have the possibility that a) the same patient's folder will be pulled
more than once (randomly) for an audit in a medical study

There is a one-to-many relationship between one Audit and many
RecordPullings; each RecordPulling relates to one Enrolment (or Folder)
and each EnrolmentFolder relates to one patient. This describes these
relationships:

MedicalExperiments -< Audits -< RecordPullings >- EnrolmentFolder


Going the other way, each enrolmentfolder may be pulled several times;
each RecordPulling is for one audit, and each Audit is for one
MedicalExperiment.

The other diagram was this:

One Patient can have several EnrolmentFolders, and each EnrolmentFolder
relates to one MedicalExperiment.

Patients -< EnrolmentFolders >- MedicalExperiments

Reading this one right-to-left gives: each MedicalExperiment has several
EnrolmentFolders, and each EnrolmentFolder relates to one Patient.

in case i haven't touched on the point of this database, there is an
interest in monitoring a) the outcome, overall, at each Audit Date for
each IRB number and b) the outcomes surrounding the patient folders
audited on that date in that IRB (e.g. which patients consent forms
were not acceptable, etc. etc.;

At some stage in the future you need to be sure that the _fields_ you put
in the tables will guarantee you can meet these objectives

these latter items would be captured
in the patient specific table.

Please don't tell me you mean one table for each patient...



Hoping some of this is starting to make some sense


Tim F