Many-to-many and form design

[Kurt]

I've posted something similar before, but haven't
received a good solution. So I'll start small: I need to
develop a database to track patients who are enrolled in
one or more studies.

1. A patient can be in many studies, and a study can have
many patients (many-to-many).
2. A patient will have multiple unique visits for each
study he is enrolled in (i.e., he can't do something for
2 studies in one visit; he'll need a separate visit for
each study).
3. The visit information (date and notes) needs to be
tracked.

I set up 3 tables. tblPatients and tblStudies are joined
by a junction table, tblVisits:

tblPatients
-------------
*PatientID
FName
LName
Address
Phone

tblStudies
------------
*StudyID
Title
Description

tblVisits (junction table)
------------
*PatientID
*StudyID
VisitDate
VisitNotes

For example, Joe is enrolled in the Green study and the
Blue study. Joe has 3 visits for the Green study, and 2
visits for the Blue study. Data in tblVisits would like
this:

tblVisits
---------------------------------------
*PatientID *StudyID VisitDate
Joe Blue 3/2/04
Joe Green 5/2/04
Joe Green 6/3/04
Joe Blue 7/1/04
Joe Green 9/8/04

I'm pretty confident that this is the appropriate design.
(Is it?) However, I'm at a loss as to how to implement
the forms to manage the data.

Here's a typical scenario for the end-user (assume that a
bunch of studies have already been entered in tblStudies):

A. Joe shows up to the clinic for the first time to be
enrolled in the Blue and Green study.

1. Add Joe (demographics, etc.) to the database.
2. Enroll him in the Blue study.
3. Enroll him in the Green study.

B. A week later, Joe comes in for his first visit for the
Blue study.

1. Pull up Joe's record, select the Blue study (perhaps
from a list showing the studies he's in?), then enter the
visit information.

C. A week later, Joe comes in for his first visit for the
Green study.

1. Pull up Joe's record, select the Green study, then
enter the visit information.

And so on for future visits.

Any idea how to proceed, set up the forms & subforms? I'm
at a loss. Thanks.

Kurt

 

[RSGinCA]

You're missing a "patient/Study" table which simply identifies the patient's
assignment to a study... Your tblVisits table doesn't serve that purpose (I'll
comment on that, below).


tblPatientStudy (or tblStudyPatient...)
----------------------
*PatientID
*StudyID

This simply identifies that a patient is enrolled in one study or another.
Down below, in your example, Joe will have 2 of these records created when he
enrolls.... before any visits.

That's all there is to the table.

It has a many-to-one relationship to the patient table (a patient can be
enrolled in one or more studies).

And it has a many-to-one relationship to the study table ( a study has many
patients).

I've posted something similar before, but haven't
received a good solution. So I'll start small: I need to
develop a database to track patients who are enrolled in
one or more studies.

1. A patient can be in many studies, and a study can have
many patients (many-to-many).
2. A patient will have multiple unique visits for each
study he is enrolled in (i.e., he can't do something for
2 studies in one visit; he'll need a separate visit for
each study).
3. The visit information (date and notes) needs to be
tracked.

I set up 3 tables. tblPatients and tblStudies are joined
by a junction table, tblVisits:

tblPatients
-------------
*PatientID
FName
LName
Address
Phone
OK


OK


tblVisits (junction table)
------------
*PatientID
*StudyID
VisitDate
VisitNotes

VisitDate needs to be part of the key. For a given patient and a given study
there will be multiple visits.

The new table I suggested above serves the need of connecting a patient to a
study.

For example, Joe is enrolled in the Green study and the
Blue study. Joe has 3 visits for the Green study, and 2
visits for the Blue study. Data in tblVisits would like
this:

tblVisits
---------------------------------------
*PatientID *StudyID VisitDate
Joe Blue 3/2/04
Joe Green 5/2/04
Joe Green 6/3/04
Joe Blue 7/1/04
Joe Green 9/8/04

I'm pretty confident that this is the appropriate design.
(Is it?) However, I'm at a loss as to how to implement
the forms to manage the data.

Here's a typical scenario for the end-user (assume that a
bunch of studies have already been entered in tblStudies):

A. Joe shows up to the clinic for the first time to be
enrolled in the Blue and Green study.

1. Add Joe (demographics, etc.) to the database.
2. Enroll him in the Blue study.

How? What records would be generated? Would you create a tblVisit record?

This is why you need the tblPatientStudy table. At this point you would have a
form to enroll him in the Blue Study. (And the Green Study.)

3. Enroll him in the Green study.

B. A week later, Joe comes in for his first visit for the
Blue study.

1. Pull up Joe's record, select the Blue study (perhaps
from a list showing the studies he's in?), then enter the
visit information.

Yes... but the record that is pulled up is that new record... tblPatientStudy.

Another appraoch is for the user to pull up the Blue Study, and select Joe from
the enrollee in that study. It could be done either way. It's probably best
to think from the user's perspective. When the user see's a patient walk in
does the user first think of him as "somebody in the blue study" or "a
patient". If each of the users of the system only deal with a single study,
then the first approach may be appropriate.

But whichever way... yes, identify the patient and the study... and enter the
info.

C. A week later, Joe comes in for his first visit for the
Green study.

1. Pull up Joe's record, select the Green study, then
enter the visit information.

And so on for future visits.

Sounds good.

Any idea how to proceed, set up the forms & subforms? I'm
at a loss. Thanks.

Let's make sure we've got the tables set up right first...

Rick

 

[Steve Schapel]

Kurt,

You will need at least a design change to the table design. You can't
use PatientID and StudyID as a composite primary key in the Visits
table, as you need to allow for duplicates of this combination. You
caould add an Autonumber field call it VisitID to this table and make
this the prinmary key, and your database will probably work ok.
However, I would recommend adding another table for Participation, to
record the Studies that each Patient is enrolled in. So here's my
suggested table structure...

tblPatients
-------------
*PatientID
FName
LName
Address
Phone

tblStudies
------------
*StudyID
Title
Description

tblParticipation
------------
*ParticipationID
PatientID
StudyID

tblVisits
------------
*VisitID
ParticipationID
VisitDate
VisitNotes

For example, Joe is enrolled in the Green study and the Blue study. Joe
has 3 visits for the Green study, and 2 visits for the Blue study. Data
in tblParticipation and tblVisits would like this:

tblParticipation
------------
*ParticipationID PatientID StudyID
1 Joe Blue
2 Joe Green

tblVisits
---------------------------------------
*VisitID ParticipationID VisitDate
1 1 3/2/04
2 2 5/2/04
3 2 6/3/04
4 1 7/1/04
5 2 9/8/04

As regards your form design, it seems to me that you will have a form
based on the Patients table, where you can enter/view/edit the record
for each patient. You will have a continuous view form based on the
Participation table, with a combobox, with its Row Source set to the
Studies table, for the entry of StudyID, to "register" the Patient in
each Study. This form will be placed as a subform on the Patients form,
with PatientID as the linking field. And you will have a form based on
a query which is based on the Participation table, this form also in
continuous view, and also placed on the Patients form as an unlinked
subform. The ParticipationID can be entered on this subform via a
multi-column combobox whose RowSource is a query based on the
Participation table, where the list is selected according to the
PatientID of the current Patient record on the main form, and where the
name of the Study is what actually shows on the form, by hiding the
ParticipationID (bound column) of the combobox.

 

[John Nurick]

Hi Kurt,

You've missed out at least one table: see inline.

I've posted something similar before, but haven't
received a good solution. So I'll start small: I need to
develop a database to track patients who are enrolled in
one or more studies.

1. A patient can be in many studies, and a study can have
many patients (many-to-many).
2. A patient will have multiple unique visits for each
study he is enrolled in (i.e., he can't do something for
2 studies in one visit; he'll need a separate visit for
each study).
3. The visit information (date and notes) needs to be
tracked.

I set up 3 tables. tblPatients and tblStudies are joined
by a junction table, tblVisits:

tblPatients
-------------
*PatientID
FName
LName
Address
Phone

tblStudies

tblPatientsStudies
PatientID )
StudyID )these two fields are the primary key
DateEnrolled
perhaps other fields

tblVisits (junction table)
------------
*PatientID
*StudyID
VisitDate
VisitNotes

This table needs a unique index on the three fields PatientID, StudyID
and VisitDate, to exclude the possibility of creating duplicate records
for the same visit.

For example, Joe is enrolled in the Green study and the
Blue study. Joe has 3 visits for the Green study, and 2
visits for the Blue study. Data in tblVisits would like
this:

tblVisits
---------------------------------------
*PatientID *StudyID VisitDate
Joe Blue 3/2/04
Joe Green 5/2/04
Joe Green 6/3/04
Joe Blue 7/1/04
Joe Green 9/8/04

I'm pretty confident that this is the appropriate design.
(Is it?) However, I'm at a loss as to how to implement
the forms to manage the data.

Here's a typical scenario for the end-user (assume that a
bunch of studies have already been entered in tblStudies):

A. Joe shows up to the clinic for the first time to be
enrolled in the Blue and Green study.

1. Add Joe (demographics, etc.) to the database.
2. Enroll him in the Blue study.
3. Enroll him in the Green study.

You'll need one form for this, bound to tblPatients and containing a
subform bound to tblPatientsStudies and linked on PatientID. So you
create one Patient record for Joe and then (in the subform) a
PatientsStudies record for each of the two studies he's enrolled in.

B. A week later, Joe comes in for his first visit for the
Blue study.

1. Pull up Joe's record, select the Blue study (perhaps
from a list showing the studies he's in?), then enter the
visit information.

There are many ways of doing this. One way is to create a form bound to
tblPatientsStudies, with two comboboxes in the header. One combobox
contains a list of Patients for the user to select from (i.e. its
RowSource is a query on tblPatients). In its AfterUpdate event, use VBA
code to set the form's Filter property so it only shows the Studies in
which the selected Patient is enrolled.

Then have a subform bound to tblVisits, linked to the main form on both
PatientID and StudyID. The subform will automatically display the
patient's previous visits, and if you create a new visit record it will
automatically pick up the current PatientID and StudyID.

 

[smk2]

Kurt:
The previous posters are correct: you are minus a few tables. I am doing this very thing and collaborating with other physicians to "standardize" our data structure in the hope that eventually this type data will be easier to share across institutions. I'd be happy to send you a little Access zip file you could look at that does exactly what you're doing. It's too much to try to put it all here.
Remove the "NoSpam" from the following email address and you can reach me at: (e-mail address removed)

 

[RSGinCA]

"You're minus a few tabes."

I'll have to remember THAT line. It sure describes a few people I know! Ha ha
ha!

Rick