Displaying data in a text box

[Guest]

I have two tables one is called Questions the other is called ansers the
primary key for each table is an ID field generated by AutoNumber. The
question ID and answer ID are linked in the relationships section.

On a form I want to display all the questions a total of 170 in various
categories and then allow a user to complete each questions thus populating
the answer table.

Do I need to add a field to the answer table which displays the questions I
have sort of already done this by linking the answer table to the question
table via the questionID field as a primary key.

Any suggestions would be great.

 

[John Vinson]

I have two tables one is called Questions the other is called ansers the
primary key for each table is an ID field generated by AutoNumber. The
question ID and answer ID are linked in the relationships section.

That's a mistake! An Autonumber is completely uncontrollable; Access
will assign a unique autonumber for each new record, but they aren't
guaranteed to be sequential. If you link an Autonumber to an
Autonumber, you have NO guarantee that the autonumber value for the
answer will have anything whatsoever to do with the autonumber value
for the question.

On a form I want to display all the questions a total of 170 in various
categories and then allow a user to complete each questions thus populating
the answer table.

Do I need to add a field to the answer table which displays the questions I
have sort of already done this by linking the answer table to the question
table via the questionID field as a primary key.

I'd suggest using three tables:

Questions
QuestionNo Autonumber Primary Key
Question Text

Users
UserID Primary Key (autonumber or some other unique field)
<any info you want about the user as a person>

Answers
QuestionNo Long Integer << foreign key to Questions
UserID <matching UserID datatype> << foreign key to Users
Answer

The Primary Key of the Answers table should be *both* the QuestionNo
and UserID fields - ctrl-click the two fields and click the Key icon.
This allows each question to be answered by many users, and for each
user to answer many questions, but prohibits one user from answering
the same question twice.

The user interface could be a Form based on Questions, with a combo
box on the form to select the UserID; the Answers table would be in a
subform using the QuestionNo and the combo box as the master link
field, and the QuestionNo and UserID fields as the Child Link Field.


John W. Vinson[MVP]