I have a survey type of database. The first table is the basic inventory elements, each one having an unique number ID
The subsequent portions are just groups of questions that need to be answered. Groups are grouped in relevancy to certain aspects.
Some groups consist of questions with same number of responses and type. In this case, with the repetition of a similar pattern, I draw relationship from this group to the inventory table by a 1-to-n relationship. So each unique ID from the inventory table, TblInv will give rise to multiple elements of a certain question group. Let's call this tblQuestiongroupG. If TblInv has 1000 elements, tblQuestiongroupG may have 15*1000 elements altogether (given there are 15 different questions in that group). Each question is distinct and marked with a type code.
Here's the scenario that I am seeing - when I come to construct a form for the respondent, I separate the entry document into a main portion and tabbed portions which correspond to each set of questions group. I will also need to construct a subform that represent every question group. I link the master ID control of the main form to the child field of each subform to make sure the appropriate subset of questions (records) are picked according to a particular inventory ID number.
I have designed a subform representing these 15 questions but I am arriving at a point that I need some guidance.
The subform consists of bound controls but because it is single view it will only look at one record at a time. Each question is a basic record in tblQuestiongroupG. I would like to populate the subform with labels representing every question and place controls for every single question. Since every question is a single record I seem to have problem getting all of them shown to these responses controls at the same time. It seems that I need a continuous view.
However, I have a problem with continuous view as well. Below is a design problem that I am dealing with:
each question has a question code which is a tag in my database to tell the group apart. Users do not really need to see and fill in it - it should be filled in automatically as users pick their responses. I am wondering if the continous view can be customized to show only certain fields of the records? On the other hand, when the user is answering question X I need to find a way to know that is the selected question and code the question type in the record as well. How do I perform this action? Each question should have a label to indicate what is being asked. When a question has never been answered, it will not have a record in the first place, it will appear that adding of new record should be internally taken care of whenever users decide to answer it. However, before a question is answered there is no record to speak of so in the display there's nothing there at all. The record needs to be generated! I am wondering if I should in fact populate my database table with the necessary records in advance, each with a code type filled in? AddNew and Delete record operation should be absent.
I also have thought along the line of designing the subform with a single view but making all the response controls unbounded. Once I get to a particular inventory the recordset will be filtered down by the master/child link relationship that will give me a recordset size of at most 15 records. With this recordset I will clone 15 new recordsets and assign to each question. Each new recordset will have operation to search the appropriate record (by question code) to look for the right records to fill in answers supplied in the control. Since the controls are unbounded I have to take care of all the display and update events by codes. Would this approach be more messy and less prefereable that using a continuous view? I do not think the coding is particular complicated but would like to hear opinions from others first.