I am designing a data-entry system for an ongoing clinical case-reading study. There are two kinds of questions for case readers to answer and input data: about 60 that require a single answer and another approximately 25 that can have more than one answer. An example of the latter type is, which necessary actions did a supervisor not take. A supervisor could have neglected one, two or many actions.
I put the fields for the questions requiring single answers in a main table and created a related table for the questions with multiple answers (one-to-many relationship). I called this second table tblDropdowns because the answer choices are in the form of dropdown lists. The questions allowing multiple answers are scattered through the hard-copy case-reading guide, whose order the data-entry system follows. Because these questions aren't grouped together, my solution was to create a separate subform attached to the tblDropdowns table for each of the 25 questions. Each subform, in datasheet view, contains a combo box for its own question.
If I had thought for a moment, I would have realized that each time data was added, Access would create a new row in tblDropdowns, meaning that the same case could have dozens of rows, each with all but one field empty, because Access had created a new row each time data was added from each subform--if the questions had been grouped together and could be displayed on the same subform, this wouldn't happen. My mistake was to assume that because the tables were related, the first response for each of the questions in the subforms would be added to the first row for that case in the tbleDropdowns table, the second on the second row, and so forth.
Anyway, the result is a table that is large and unwieldy. Is there a better solution?
Many thanks,
Henry
I put the fields for the questions requiring single answers in a main table and created a related table for the questions with multiple answers (one-to-many relationship). I called this second table tblDropdowns because the answer choices are in the form of dropdown lists. The questions allowing multiple answers are scattered through the hard-copy case-reading guide, whose order the data-entry system follows. Because these questions aren't grouped together, my solution was to create a separate subform attached to the tblDropdowns table for each of the 25 questions. Each subform, in datasheet view, contains a combo box for its own question.
If I had thought for a moment, I would have realized that each time data was added, Access would create a new row in tblDropdowns, meaning that the same case could have dozens of rows, each with all but one field empty, because Access had created a new row each time data was added from each subform--if the questions had been grouped together and could be displayed on the same subform, this wouldn't happen. My mistake was to assume that because the tables were related, the first response for each of the questions in the subforms would be added to the first row for that case in the tbleDropdowns table, the second on the second row, and so forth.
Anyway, the result is a table that is large and unwieldy. Is there a better solution?
Many thanks,
Henry