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

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

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

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

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