R
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
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