If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Normalised table headache
I have nicely normalised tables for a questionai table 1 holds questions
and question-ID's, table 2 holds Possible responses and response-ID's, and table 3 is a join table with its own ID field , the two ID fields from tables 1 + 2 (as foreign keys) and a few other details ( service, date etc), which also joins up to the rest of the database... All good. How do I go about making a form to enter questionaire answers? All the questions must be answered for each service, so I need to filter by service. This is day 1 access level stuff I know, and with un normalized tables I would be fine, but I cannot get this to go! I need to select service and then be able to lookup an answer for EVERY question. Having a datasheet/ continuous form setup where each row is a question won't do it because i need to see all of the questions at the outset. Please help |
#2
|
|||
|
|||
Normalised table headache
zilla,
This structure makes sense *only if* a response may belong to more then one question, Is this is the case? What is the significance of "service"? Please elaborate Regards Zilla wrote: I have nicely normalised tables for a questionai table 1 holds questions and question-ID's, table 2 holds Possible responses and response-ID's, and table 3 is a join table with its own ID field , the two ID fields from tables 1 + 2 (as foreign keys) and a few other details ( service, date etc), which also joins up to the rest of the database... All good. How do I go about making a form to enter questionaire answers? All the questions must be answered for each service, so I need to filter by service. This is day 1 access level stuff I know, and with un normalized tables I would be fine, but I cannot get this to go! I need to select service and then be able to lookup an answer for EVERY question. Having a datasheet/ continuous form setup where each row is a question won't do it because i need to see all of the questions at the outset. Please help |
#3
|
|||
|
|||
Normalised table headache
Hi Zilla,
You might want to give Access MVP Duane Hookom's "At your survey" sample a try: http://www.rogersaccesslibrary.com/O...p#Hookom,Duane Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Zilla" wrote: I have nicely normalised tables for a questionai table 1 holds questions and question-ID's, table 2 holds Possible responses and response-ID's, and table 3 is a join table with its own ID field , the two ID fields from tables 1 + 2 (as foreign keys) and a few other details ( service, date etc), which also joins up to the rest of the database... All good. How do I go about making a form to enter questionaire answers? All the questions must be answered for each service, so I need to filter by service. This is day 1 access level stuff I know, and with un normalized tables I would be fine, but I cannot get this to go! I need to select service and then be able to lookup an answer for EVERY question. Having a datasheet/ continuous form setup where each row is a question won't do it because i need to see all of the questions at the outset. Please help |
#4
|
|||
|
|||
Normalised table headache
Jacob, Tom thanks for your responses.
Yes - for the majority of the questions on the questionnaire there are only 5 allowed answers, so yes this structure does apply. (For certain questions its textual responses only, but that is a different and simpler problem) Tom - I have already have a look at the At Your Survey, but I found it rarther confused and confusing...... The problem, to restate, is to have a form structure that displays all questions allowing me to give any one of a number of preestablished anwers to it. Given a QuestionAnswer table holding only ID numbers, where rows are added only as a question gets answered, how can this work - what is the forms datasource etc? Help greatly appreciated "Tom Wickerath" wrote: Hi Zilla, You might want to give Access MVP Duane Hookom's "At your survey" sample a try: http://www.rogersaccesslibrary.com/O...p#Hookom,Duane Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Zilla" wrote: I have nicely normalised tables for a questionai table 1 holds questions and question-ID's, table 2 holds Possible responses and response-ID's, and table 3 is a join table with its own ID field , the two ID fields from tables 1 + 2 (as foreign keys) and a few other details ( service, date etc), which also joins up to the rest of the database... All good. How do I go about making a form to enter questionaire answers? All the questions must be answered for each service, so I need to filter by service. This is day 1 access level stuff I know, and with un normalized tables I would be fine, but I cannot get this to go! I need to select service and then be able to lookup an answer for EVERY question. Having a datasheet/ continuous form setup where each row is a question won't do it because i need to see all of the questions at the outset. Please help |
#5
|
|||
|
|||
Normalised table headache
Zilla,
I assume nothing so please bear up with me if I include something which you already done. Table 3 is in fact a "Junction Table", Both ID's have to be a joint key to avoid a duplication of a combination of ID's from table 1 and table 2. Also each key need to have one-to-many relation with it relevant ID in Tables 1 +2 whereby the many side is on the table 3 In order to populate table 3, both questions and possible answers have to be created first. You can however create the questions "on the run" (see below) To associate questions and answer: 1. Create a query using the three tables. Select the fields and make sure that Both questionID and AnswerID comes from the Junction table (your table 3), otherwise your query will be un-updateable. 2. Create a Question Main Form Based on table 1. 3. Create answers Continuous form, based on the query above. Use a combo box to select AnswerID's that are already created. 4. Bring the answers form as a sub-form of the main form, link them on QuestionID Now you are able to add/edit the questions in the main form and select the possible answers for each question from the sub form as well as edit them if required. I hope that this helps. Regards Jacob Zilla wrote: Jacob, Tom thanks for your responses. Yes - for the majority of the questions on the questionnaire there are only 5 allowed answers, so yes this structure does apply. (For certain questions its textual responses only, but that is a different and simpler problem) Tom - I have already have a look at the At Your Survey, but I found it rarther confused and confusing...... The problem, to restate, is to have a form structure that displays all questions allowing me to give any one of a number of preestablished anwers to it. Given a QuestionAnswer table holding only ID numbers, where rows are added only as a question gets answered, how can this work - what is the forms datasource etc? Help greatly appreciated |
Thread Tools | |
Display Modes | |
|
|