Hello,
I’m going round in circles trying to design a schema to allow me to build a database for a series of medical studies. (In access 2010)
For each study I will receive an excel table like the two examples below (simplified version of a real one). For now I’m not thinking about how I will import it into Access but will do once I’ve got the Access principle sorted. I know that I will have to split the tables into normal form.
Every patient who is recruited to each study WILL attend all the appointment (cycles) and all the specified Activities that pertain (ie the Y’s) to that appointment (the ‘Y’s will be represented in tbl_Patient_study_appointment_activity). Each study could have a different number of appointments and activities.
For example, every patient who is recruited to the Diabetes study will have all 7 appointments (cycles) and at appointment 3 will undergo ‘Medical History’ and ‘ECOG Status’.
Each patient could be on more than one study.
I have used a variety of junction tables to achieve what I think does the job for me, although I do wonder if I am over complicating it. Could anyone help or give feedback? (I have been reading up about ternary relationships but don’t fully understand how they work. Maybe these could work?)
Once I am satisfied that this schema does will work. I need to find a way of when I add a patient to a study, that they inherit the pre-determined appointments and activities. Am I right in saying that this would be a series of append queries? Or could I use a form which when I add a patient to a study they automatically get populated in the ‘tbl_Patient_Study_Appointment’ and ‘tbl_Patient_Study_Appointment_Activity’
Once I’ve done that I need to work out how I will upload a new study and schedule into the database.
If anyone could help I would be most grateful. I know there are some gapping holes in my understanding of Access.