The problem I'm having deals with some questions having the option to "select all that apply." For example, one question deals asks, "What conditions did this patient have prior to admission? Select all that apply" and below 30 or so different medical conditions. Then below that, another question might ask, "What procedures were performed after the patient was admitted. Select all that apply" followed by another large list of procedures. And below that a question asking about all possible drugs a patient was given.
So, I realized because of the "select all that apply" option, I had to list out every condition, procedure, drug, etc, as a separate field with a yes/no option. So instead of having a field that said "medical_conditions_prior" with hypotension, chf, diabetes, etc. as options, I had to create fields for each conditions, such that my fields were "medical_conditions_prior_hypotension", "medical_conditions_prior_chf", "medical_conditions_prior_diabetes", etc, and the options were "yes/no."
However, I recieved an error message saying I had two many fields. So I changed my approach and tried to create a primary table and then a table for each survey question that had "select all that apply" option. So, in my primary table, I would have a field that said "medical_conditions_prior" with a yes/no option. And I would create a table with all the medical conditions with a yes/no option. But, I have no idea how to correctly link these tables, as there are no two fields in the table that match, such as a primary table organized by novels and a secondary table organized by authors, but still having a novels field.
Where you have a question that can have multiple answers, I would populate a combo box, or list box from a table that would hold all the possible answers. This one table would hold ALL the possible answers for EVERY question that will allow multiple answers. That means you will need a field in this "Answer" table for a reference to the question. That will allow this one table to hold the multiple answers for multiple questions. Then, when the user selects multiple items in the combo or list box, you need to store those selected RecordIDs into your "SelectedAnswers" table. And, again, this "selectedAnswers" table will need a reference to the question number. What I think would help you greatly in this design would be to learn about Normalization of Data. If you follow the link in my signature area that is titled Access Design Tips, you can read the first linked article on that web page. Read it, study it, and then come back with any questions you may have. The time spent in learning Normalization will pay you back within the first few months. No more than a year for the return on investment in Normalizing your data.