Tracking patient visits for multiple studies

[Hoving]

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.

 

[jrbarnett]

The easiest way would be to put PatientID into tblVisits.

You can then easily identify the patient that that the visit relates to (assuming that a visit can be for one and only one patient at a time).

John

 

[swaybright]

From looking at your tables, the tbllinkstudiespatients can be combined with your visits table. Unless you can have patients that visit who aren't in a study. I would just put all the data from the visits table into the link... table.

In general, if you have a many-to-many relationship, there is some data that results from that relationship. In this case, visits.

Shane

 

[Hoving]

Shane:

I moved the visit info. into the tblLinkStudies_Patients. This did improve things. 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

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

Is my problem with the table design or with how the subforms are related?

Thanks again.

Kurt

 

[swaybright]

Yes, the problem can be either place. Take a step back and think about how the data in each table relates to the other data in other tables. Remember, you always design a database with the desired outputs in mind.

You can attack this several ways.
1) Does tblStudyStatus relate to tblLinkStudies_Patients? Maybe you have a one-to-many with StudyStatus and Visits, but be careful. Will a patient do two studies in a single visit--if so how will that data be represented?
This is the table design question.

2) Quick fix: Change your query to:

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

The criteria should be whatever value the database stores Enrolled patients. The syntax may be a little off in the SQL but basically pull your info from the tblStudyStatus rather than the subquery.

In the form we will have to set the Link Master Fields property of your Visits subform to PatientID and StudyID in the Studies Subform

 fsubStudies.Form!PatientID;fsubStudies.Form!StudyID

And put a little code behind the on current event of your Studies subform

Me.Parent!fsubVisits

Note: This code will show only the visits for the selected study in the Studies subform. If you want to see all visits regardless of study, omit the code.

Shane

 

[Hoving]

> 1) Does tblStudyStatus relate to
> tblLinkStudies_Patients? Maybe you have a one-to-many
> with StudyStatus and Visits, but be careful.
> Will a patient do two studies in a single visit--if
> so how will that data be represented?

A patient will never do two studies in a single visit.

> 2) Quick fix: . . .

Just want to make sure I'm reading this correctly:

> In the form we will have to set the Link Master Fields
> property of your Visits subform to PatientID and StudyID

Like this:

Link Child Fields: PatientID;StudyID (no change)
Link Master Fields: PatientID;StudyID (instead of
PatientID;LinkStudyID)

> in the Studies Subform
> fsubStudies.Form!PatientID;fsubStudies.Form!StudyID

Are you saying to change the links for fsubStudies to:

Link Child Field: PatientID (no change)
Link Master Field: fsubStudies.Form!_
PatientID;fsubStudies.Form!StudyID
(instead of PatientID)

> And put a little code behind the on current event> of your Studies subform
> Me.Parent!fsubVisits

This gives a "Compile error: Expected:=" Should there a bit more to this code?

Thanks.

 

[swaybright]

Sorry for being unclear, I was writing this while doing other things.

The Link Master Fields property of your Visits subform should read:

fsubStudies.Form!PatientID;fsubStudies.Form!StudyID

This makes the Visits subform use the Studies subform to get the appropriate records. i.e. those related to the desired patient and the selected study.

Don't change the studies subform

The code should read:

Me.Parent![fsubvisits].Requery

Sorry for the confusion. It is well known that men don't multitask well. Certainly true in my case.

A little background on what I did...

Your mainform is info about the patient. The studies subform contains a list of the studies in which the patient participates. That data is contained in 3 tables: tblStudyStatus, tblStudies, tblStaff. So we created a query to flatten this data. If you just run the query you will see that it has all the data for all the studies with patientids. We limit this data set by enrolled so we weed out all the merely interested patients. Since we want the subform to just contain the studies in which said patient participates, we set the master and child links to patientID on the main form.
The visits subform will contain information about each visit. If a patient is participating in more than study then we may want to keep those visits "separate". To do that, we create a subform that contains the info and we set the master and child links to the studies subform. In order to keep it current, we put the code behind the studies form to requery the visits subform each time you select a study. Alternatively, you could just list all the visits regardless of the study and then you would just have to link to the patientid of the main form.
I hope that helps make everything more clear


Shane