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

 

[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.

 

[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?

 

[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.