Tracking patient visits for different studies

[Kurt]

Here is a link to the relationships window for a database
used to keep track of patients who are enrolled in one or
more studies.

http://members.cox.net/kheisler6/Studies.jpg

A patient can be enrolled in more than one study. A study
can have more than one patient. (Many-to-Many)

Subjects will need to show up for any number of visits
for each study they're enrolled in. For example, Joe is
enrolled in the Milk study and the Flu study. He may have
4 visits for the Milk Study, and 3 visits for the Flu
study.

To simplify things, let's assume that the only thing to
record for each visit, for each subject, is: Visit Date
and Visit Outcome (e.g., no show, cancelled, completed).
But this visit information needs to be related to *that
particular patient* for *that particular study*.

(To visualize it on a form, imagine you are on Joe's
record on the Patients form. A datasheet subform would
have two records, showing that Joe is in the Milk and Flu
studies. You would click on the Milk record, and then
enter visit information - e.g., on another subform linked
to the studies subform - for Joe's visit.)

In my current relationship window, the visit information
is related only to the studies, but not the patients. How
should I restructure things?

Thanks.

 

[Tim Ferguson]

A patient can be enrolled in more than one study. A study
can have more than one patient. (Many-to-Many)

Okay: but looking at your diagram you seem to have two separate
relationships between Studies and Patients -- one called tblStudyStatus and
the other called tblLinkStudies_Patients. What are the meanings of these
two relationships and how are they different?

In my current relationship window, the visit information
is related only to the studies, but not the patients. How
should I restructure things?

Well, if a Visit belongs to a Patient as well as a Study, then it needs two
Foreign Keys... In fact, it's more constrained than that, since a Visit can
only relate to a Patient and a Study that are already related to eachother
-- this seems to be the tblStudyStatus.

If I were you, I would dispense with the tblStudyStatus.StatusID and make
the pair (PatientID, StudyID) into the PK of that table, and then relate
tblVisits to that (and remember _no_ relationships to tblStudies or
tblPatients).

Incidentally, what does tblStudies.PrincipalInvestigatorID relate to?
Shouldn't this be either (a) tblDoctors or (b) tblStaff?



Hope that helps


Tim F

 

[John Vinson]

In my current relationship window, the visit information
is related only to the studies, but not the patients. How
should I restructure things?

I'd say your Visit table should be the resolver table between a Study
and a Patient: evidently each Visit pertains to one study, and it
obviously pertains to one Patient (the one that's visiting!)

You might want the Primary Key of visits to consist of three fields:
PatientID, StudyID, and VisitDate. This would prevent double entry of
the same visit but allow a patient to have visits to two or more
studies on the same date, or to the same study on different dates.

 

[Kurt]

I moved the visit information into the
tblLinkStudies_Patients, which seems to be a move in the
right direction. The new relationship window is here:

http://members.cox.net/kheisler6/Studies2.jpg

This way, if Joe has 1 visit for the Milk study:

tblLinkStudies_Patients
----------------------------
StudyID PatientID VisitDate
Milk Joe, Smith 2/5/2004

If I add another visit for Joe for the Milk Study:

tblLinkStudies_Patients
----------------------------
StudyID PatientID VisitDate
Milk Joe, Smith 2/5/2004
Milk Joe, Smith 2/7/2004

This appears correct.

However, when I'm on Joe's record on the Patients form:
If I enter more than one visit for Joe for the Milk
study, there will be 2 records showing 'Milk' in the
Studies subform. If I enter a 3rd visit, Milk will show 3
times.

For example:

http://members.cox.net/kheisler6/Patients.jpg

(Similarly, on the Studies form, Joe will be listed 3
times in the Patient subform for the Milk study.)

A screenshot of the Patients form in design view is here:

http://members.cox.net/kheisler6/PatientsForm.gif

frmPatients is based on tblPatients. It has two subforms,
fsubStudies and fsubVisits, which are linked together so
when you click on a study in fsubStudies, the visit
information for that study will appear in fsubVisits.

fsubStudies is based on:

SELECT DISTINCTROW tblStudies.Title,
tblStudies.Description, tblStudies.StudyID, [Patient
Subform Subquery].PatientID, tblStaff.LName & ", " &
[FName] AS Manager
FROM (tblStaff INNER JOIN tblStudies ON
tblStaff.StaffID=tblStudies.ProjectManagerID)
INNER JOIN [Patient Subform Subquery] ON
tblStudies.StudyID=[Patient Subform Subquery].StudyID;

fsubVisits is based on:

tblLinkStudies_Patients

--------------------------------

Did my change to the table design cause this, or is my
problem with how the subforms are related?

Thanks again.

Kurt

 

[Kurt]

A patient can be enrolled in more than one study.

Okay: but looking at your diagram you seem to
have two separate relationships between Studies
and Patients -- one called tblStudyStatus and the
other called tblLinkStudies_Patients. What are the
meanings of these two relationships and how are they
different?

I was saving my questions about tblStudyStatus for later.
In the end, I will need to keep track of two separate
entities for each study for each patient: Visits & Study
Status. For example, Joe Smith might be enrolled in the
Milk and Flu studies. He will have different visits for
each study, and a different status for each study (e.g.,
we may withdraw from the Flu study, but stay in the Milk
study).

If I were you, I would dispense with the
tblStudyStatus.StatusID and make the pair
(PatientID, StudyID) into the PK of that table,
and then relate tblVisits to that (and remember
_no_ relationships to tblStudies or tblPatients).

If my recent change (see my other reply) is the wrong
approach, I'll try this suggestion.

then relate tblVisits to that (and remember
_no_ relationships to tblStudies or tblPatients).

So delete any relationship between tblVisits and
tblStudies, and between tblVisits and tblPatients?

Also, if I create a combined PK (PatientID, StudyID) for
tblStudyStatus, which key do I use to relate the table to
tblVisits?

Incidentally, what does
tblStudies.PrincipalInvestigatorID relate to?
Shouldn't this be either (a) tblDoctors or (b)
tblStaff?

tblStaff. Just hadn't made the relationship yet.

Hope that helps
Tim F

It does. Thanks.

 

[Tim Ferguson]

In the end, I will need to keep track of two separate
entities for each study for each patient: Visits & Study
Status.

No problem... this is quite right. It's tblLink_Whatever that did not seem
to have any relationship (haha) to the real world.

So delete any relationship between tblVisits and
tblStudies, and between tblVisits and tblPatients?

Correct. You don't want to record a Visit for a particular Study for a
Patient who isn't enrolled on that study: so that is the actual constraint
for the Visits table. There is no sense in putting in extra contraints,
because the first one won't allow non-existent Studies or Patiets in the
Visits table anyway.

Also, if I create a combined PK (PatientID, StudyID) for
tblStudyStatus, which key do I use to relate the table to
tblVisits?

(PatientID, StudyID) -- a FK always matches the PK exactly.


Hope it helps


Tim F