Study Database Relationships

[ResearchAsst1]

I've been working on a database containing patient information, information
on the different studies, and sponsor information for the research people at
a clinic I work at.

I have a basic patient table containing contact information (primary key
being an Autonumber to give them a PatientID). Also in the table are 3
ParticipatedIn fields (numbered 1, 2, and 3) that contain the protocol # for
up to 3 different studies that they may have participated in.

My second table is Study Info, which contains the Sponsor (whoever organized
the study), the StudyProtocolID (Primary Key), type, phase, etc.

The third table is Sponsors, which contains the SponsorName (primary key)
and some basic contact information.

I also have forms for studies, patients, and sponsors set up so that the
doctors won't have to touch the actual layout of the table when inputting new
stuff.

I've succeeded in setting up relationships so that the Study and Sponsor
tables will show the FIRST study the patients have been in (ParticipatedIn1)
by using one-to-many relationships (ParticipatedIn1 to StudyProtocolID and
then Sponsor to Sponsor). But I want to set it up so that ParticipatedIn2 and
ParticipatedIn3 are also linked to the StudyProtocolID.

The final ideal design would have, for example, anyone who has study
protocol "XYZ1" listed in one of their 'ParticipatedIn' fields to show up in
the little drop down menus for that study (both in the Study table under that
protocol number, and in the Sponsor table after opening the sponsor and then
the study underneath it).

Forgive me for being wordy, not sure if I needed to write all that out to
make my question clear, but do you understand my problem and have any ideas
on how to make this work? Reorganizing tables is doable if necessary.

Thanks for the help,
Ben

 

[Damon Heron]

If I am reading it correctly, I get this:
1 Patient may have one or more Studies.
1 Sponsor may have one or more Studies.
So i would design the tables like this:
tblPatient
PatientID
Name
other patient info fields - (omit the participatedin fields)

tbl Study Protocol
Protocol ID
Type
Phase
PatientID (foreign key to tblPatient)
SponsorID (foreign key to tblSponsors)

tblSponsors
SponsorID
EntityName
etc.

Your Study Form could have a subform with the patientID link, showing all
the patients involved with that study.
Your Sponsor form could have a subform with all of the Studies for each
sponsor.

Damon

 

[ResearchAsst1]

Okay, the subdatasheet in the Sponsor table I was able to figure out no
problem, I had managed to do that before. Where I am having trouble is the
tblStudy to tblPatient link, how exactly would that look? The only
guaranteed unique thing about each study is the ProtocolID, so I would've
thought that putting that into each patient's info somewhere would be the way
to link them. And I made it work, but not with patient's who have multiple
studies.

So how exactly would your method be set up, like in the subdatasheet's
little wizard, what do I choose? I've tried a few things and it seems to end
up with 'this is too complex an expression' or it just shows the study again
when I open the subform instead of showing the patient's information.

Thanks again for the help, the first response was helpful I just managed to
get myself stuck again.

 

[Damon Heron]

I do not use the "subdatasheet wizard", so can't help you much with that
one. Using my tables as described below, though, and you can open the
Relationships window, add the tables, and add the links from tblPatient to
tblStudy with no problem. Tables are for establishing relationships and
holding data, not for user interface. That is what forms are for. In the
design of your form, you can add a subform(with the record source
tblPatients) that is linked by the unique ID of each table. When the form
is opened in design view, click on the OUTSIDE of the subform so it is
highlighted, and (in the properties window) select the Data tab. There you
will see Link Master Fields and Link Child fields. Click the little button
on the right of the field and you can then add the PatientID link. So when
the form is opened to a particular study, the patients involved in that
study will show up in the subform.
You are correct that the protocol ID is unique to each study, but so is the
PatientID unique to each patient. Think of the linking like this: what
happens if the protocol were to change and you could have a patient involved
in 4 or 5 studies? You would have to redesign your table to account for
that change. By using the PatientID as the foreignkey in tblStudies, you
can have the patient involved in 1 or 100 studies, without changing the
tables. Once the tables are established and data begins accumulating, you
can see the wisdom of having a good foundation to build on.

Damon

 

[ResearchAsst1]

That helped a lot thank you, I had made forms for the inputting of data but I
didn't realize you could make subforms so my head was stuck in tables. Thanks
for the help though, it seems to be working properly with my dummy patients
that I'm testing it with.

Thanks again,
Ben

 

[ResearchAsst1]

Well, it looked good for a few minutes, then I realized that when I entered
patient's through the form, the autonumber was assigning them all zeroes. And
then it began complaining because of the repeat in the primary key. I was
able to fix the primary key, but then they stopped being linked to the
studies because the studies were all assigned a patientID of zero, and since
there were no patients with a zero anymore none of them were linked? With the
autonumber for patientID set up, it assigns each an individual number and
then they aren't linked to the studies anymore because the studies are all
being given a value of 0 in their patientID field, but if I give a study a
value of 1, then only patient 1 gets listed... how to I get it so that
multiple patients are involved?

 

[Damon Heron]

I should have reversed the form - Patient main form and studies subform.
Also, the Studies ID should be an autonumber, but the StudiesProtocol should
be one you assign. On the studies subform, for each patient, you would
enter the StudiesProtocol, the Type, and the Phase. Autonumbers are
assigned as the record is completed automatically, and don't need to be
shown.

so main form has Patient Jones, ID #34, who is involved in 2 studies....
the subform has Protocol A10, Type EKG, Phase 1.-- Protocol A11,Type Liver,
Phase 1, etc.

The next record would be another patient, Smith, and you would add the
studies for him.

Note that the Studies table will grow as you are really showing all the
patients for all the studies. You could have ANOTHER table, StudiesType,
that lists all the study data, and have a StudyTypeID foreign key in the
Studies table. Then on the subform you could use a combo box with the
ControlSource the StudiesTypeID, and the RowSource the new StudiesType
table.

Damon

 

[Steve]

I suggest a revision to Damon's tables:
tblPatient
PatientID
Name
other patient info fields - (omit the participatedin fields)

tblSponsors
SponsorID
EntityName
etc.

TblStudyType
StudyTypeID
StudyType

TblStudyPhase
StudyPhaseID
StudyPhase

tbl Study Protocol
Protocol ID
SponsorID (foreign key to tblSponsors)
StudyTypeID (foreign key to tblStudyType)
StudyPhaseID (foreign key to TblStudyPhase)

TblStudyProtocolPatient
StudyProtocolPatientID
Protocol ID
PatientID
StudyProtocolPatientStartDate
StudyProtocolPatientEndDate

To enter study protocol data you would use a main form based on tbl Study
Protocol and a subform based on

TblStudyProtocolPatient. The main form would be a single form and the
subform would be a continuous form. The link master property would be
Protocol ID in tbl Study Protocol and the link child property would be
Protocol ID inTblStudyProtocol Patient.

The fields in your tables might not be precisely what you need. If you jave
questions ask away.

BTW, if it is not too inconvenient, I would also suggest removing all spaces
in tabble and field names. You'll find working with your database will be
much easier.

Steve

 

[Harlan]

Ben,
Also, be careful designing databases with patient info.....HIPA has some
strigent requirements for data retention....

 

[ResearchAsst1]

Thanks yeah, we've been checking up on HIPAA guidelines while developing this
database.