Simple yet complex

[Andy]

Hi,

I am trying to build a very simple database, but I just
can't seem to figure out how to implement it... Any
suggestions / help is much appreciated.

OK, here's the problem:

The database will be presented like a questionnaire. I
will have 3 tables, The first is the User table, that will
have a user_id Primary key and just some general info
(address, age, sex, etc...). The second is the Question
Table, which will have a question_id primary key, and
a 'question' field. This table will obviously hold a list
of the questions that will be asked on the form. The last
table is the answers table, which will have an answer_id
primary key, and will have indexes: user_id and
question_id, which will have relationships back to the
other two tables.

i.e.

USER
----
*User_id
age
sex
address
tel


QUESTIONS
---------
*question_id
question


ANSWERS
-------
*answer_id
user_id (points to USERS.user_id)
question_id (points to QUESTIONS.question_id)
answer


Ok, with all that explained, My problem is building the
form... I need the form to have a list of all the
questions, with a text box for the answer next to it. This
also needs to automatically change whenever a new question
is added or deleted.
The user should submit all their details at the top (which
updates the USER table), then will start to enter answers
to each question.
Once submitted, this will create a new answer_id for each
question, update the ANSWERS table with the new User_id,
each question_id (from the QUESTIONS table) and the
corresponding answer...

Does this make sense?

Any help would be greatly appreciated, because I really
don't know how to create this Form...

Regards.

Andy

 

[Russ]

If you have just one questionaire to do then simply have
all teh questions on the form and the user id. Then in
code run an apend query to the answers table. As you know
the question id for each text box on teh form .. the sql
would look something nlike this.

docmd.runsql("insert into [Answers Table]
(QuestionID,UserID,Answer) Values ('QuestionID','The Users
Id From the form','there answer to the question')


If you are looking to ask multiple questionaires then open
a record set and cycle the questions. so you are not
limited to developing a form for each quesionaire.

Hope this helps. Russ