Form Problem

[Nick]

i

I'm creating a database which will capture the answers to
a questionaire with 25 questions.

I have 3 tables - Contact - Questions - Answers

1. Contacts Table

ContactID [auto number]

First Name
Last Name
etc etc

2. Questions Table

QuestionID [auto number]
Question Number (ie 1.1, .1.2, 2.1)
Question

3. Answer Table
AnswerID [auto number]
QuestionID
ContactID
Answer

I have created a one to many relationship (also enforcing
referential integrity) from the:
Contact table to the Answer table
ContactID to ContactID

and
I have create a one to many relationship (also enforcing
referential integrity) from
Question Table to the Answer table
QuestionID to QuestionID

What I am trying to do

Not all 1000 contacts in the ContactsID will reply to the
survey.
I want to create a form where I can call up respondent
contact details for those who return the questionaire and
then enter their answers to the 25 questions. I want to be
able to see all the 25 questions with an answer box
underneath - I do not wish to click through each question
one at a time.

How can I achieve getting all 25 questions (with an answer
space below each question) to appear with the contact
details. No matter what I have tried only only one
question box (without any text in it) and one answer box
is appearing.

I do not know any VB so a step by step answer would be
much appreciated. I have been struggling with this problem
for over a week and have asked many people but no on seem
to be able to figure this out.

Many thanks

 

[John Vinson]

How can I achieve getting all 25 questions (with an answer
space below each question) to appear with the contact
details. No matter what I have tried only only one
question box (without any text in it) and one answer box
is appearing.

A Form based on the contact table with a Subform should do it. You can
base the Subform on a query joining the Question table to the Answer
table, using a "left outer join" - "show all records in Questions and
matching records in Answers"; this will display all 25 questions and
allow the user to fill in the answer.

 

[Guest]

-----Original Message-----


A Form based on the contact table with a Subform should do it. You can
base the Subform on a query joining the Question table to the Answer
table, using a "left outer join" - "show all records in Questions and
matching records in Answers"; this will display all 25 questions and
allow the user to fill in the answer.


.
Hi John

This is the furest I have gotton. I created a left outer
join, then I created a query from the Question and Answer
table and then all the records appeared. However when I
went to create a form using this query and fields from my
contact table I got the message "you have chosen fields
from the record sources which wizard cannot connect. You
may have chosen fields from a table and from a query based
on that table. If so try chosing either fields from the
query or the table".

However in the query I did not use any firlds from the
contact table, however the Contact table has a one to many
relationship with the Answer table ContactID = ContactID.
However I didnot use Contact ID in my query but aatempted
to add it (from the Contact Table) when attempting to
create a form.

I've provided my database structure underneath
I have 3 tables - Contact - Questions - Answers

1. Contacts Table

ContactID [auto number]

First Name
Last Name
etc etc

2. Questions Table

QuestionID [auto number]
Question Number (ie 1.1, .1.2, 2.1)
Question

3. Answer Table
AnswerID [auto numbeer]
QuestionID
ContactID
Answer

I have created a one to many relationship (with enforced
referential integrity) from the:
Contact table to the Answer table
ContactID to ContactID

and
I have create a one to many relationship with enforced
referential integrity) from
Question Table to the Answer table
QuestionID to QuestionID

Thanks for your help.

 

[John Vinson]

This is the furest I have gotton. I created a left outer
join, then I created a query from the Question and Answer
table and then all the records appeared. However when I
went to create a form using this query and fields from my
contact table I got the message "you have chosen fields
from the record sources which wizard cannot connect. You
may have chosen fields from a table and from a query based
on that table. If so try chosing either fields from the
query or the table".

However in the query I did not use any firlds from the
contact table, however the Contact table has a one to many
relationship with the Answer table ContactID = ContactID.
However I didnot use Contact ID in my query but aatempted
to add it (from the Contact Table) when attempting to
create a form.

I've provided my database structure underneath
I have 3 tables - Contact - Questions - Answers

Your tables look fine.

I think the problem is that you're trying too hard on the form!

Base a Form on the Contacts table - *just* the contacts table.

Base a second Form on a Query left-joining Questions to Answers by
QuestionID. Be sure to include BOTH instances of QuestionID -
Questions.QuestionID and Answers.QuestionID - in the query.

Drag this second form onto the first form in design view, to create a
Subform. Use ContactID as the master/child link field of the subform.

This should give you what you need!