Unanswered: Database design: Clinical trial with 900 unique questions for 200 unique patients
I am seeking advice for building a database for a clinical trial in surgery and medicine.
Approximately 200 patients are going to answer more than 900 questions split out on different questionaries they get handed out as printed paper. They do not answer all the questionaries at once, but at seven different visits at the doctor, the last being six months after surgery. All these papers needs to be recorded into a database as data, and later exported as tables to an statistics program called SPSS.
With MS Access it is possible to make an navigational form with one pane or subpane for each of the different questionaries to be recorded about the patient. It has then been requested that all data is visible from one form only, and with the following structure:
To the left: An identification-area where the patient with his/hers inclusion number, and other static values like "gender" and "birthdate". The inclusion number -field needs an search function, so it is possible to find and continue recording on patients already in the records (already in one or mere tables). It is not practically possible to have all data about one patient ready at the same time.
To the right: Different navigational panes (one for each questionary). It is crucial that these panes are all connected to the identification-area to the left, so data from one patient stays connected to the excact same patient.
My idea is to first make one tabel for each questionary, with question names as field names, then an autonumber field (QuestionaryAnswerID), and then one identification field to connect records from all the different tabels to one patient (the patients inclusion number).
Then I make the navigation form with panes for each tabel, and finish the form-panes off by making some predefines answers as dropdown menues on those fields that allow predefined answers (all numerical/boolean).