Mixed up with Relationships..help!

Ok, I have managed to get myself completely confused.

I have a table, called tblstudent, which has a series of questions in it, as
in data entry for a questionnaire.

I have read some books about relationships and the trouble i have is that
several of the questions have the same choice of answer, for example,
tblagreetype. ie. strongly agree, slighly agree, slighlty disagree etc. When
i try to create the lookup table to link to this table, the relationships
window looks all over the place.

Each question for agreetype as the lookup field replicates the table, so 4
questions mean 4 fields in tblstudent called agreetypeID and the
relationships show tblagreetype to tblagreetype1, 2 and 3! Is this normal?
Should this be a one to many relationship? I'm also confused about many to
many relationships, so i can use subforms to show multiple field answers too.
I'm guessing the relationships are the problem but i cannot seem to solve it!

--
Regards

KrazyRed

"KrazyRed" wrote in message
...
Ok, I have managed to get myself completely confused.

I have a table, called tblstudent, which has a series of questions
in it, as
in data entry for a questionnaire.

The name tblstudent implies that the table is about students, not
about questions.

That table sounds like it should be called QuestionaireAnswers.


I have read some books about relationships and the trouble i have is
that
several of the questions have the same choice of answer, for
example,
tblagreetype. ie. strongly agree, slighly agree, slighlty disagree
etc. When
i try to create the lookup table to link to this table, the
relationships
window looks all over the place.

The Tables would go like this:

CREATE TABLE Students
(StudentID AUTOINCREMENT
,NameFirst TEXT(72)
,NameMiddle TEXT(72)
,NameLast TEXT(72)
,CONSTRAINT pk_StudentID PRIMARY KEY (StudentID)
)

CREATE TABLE Faculty
(FacultyID AUTOINCREMENT
,NameFirst TEXT(72)
,NameMiddle TEXT(72)
,NameLast TEXT(72)
,CONSTRAINT pk_Faculty PRIMARY KEY (StudentID)
)

CREATE TABLE Questions
(QuestionID AUTOINCREMENT
,Question TEXT(255) NOT NULL
,CONSTRAINT pk_Questions PRIMARY KEY (QuestionID)
)

CREATE TABLE Answers
(AnswerID AUTOINCREMENT
,QuestionID LONG NOT NULL
,Answer TEXT(255) NOT NULL
,CONSTRAINT pk_Answers PRIMARY KEY (AnswerID)
,CONSTRAINT fk_Answers_Questions FOREIGN KEY (QuestionID)
REFERENCES Questions (QuestionID)
)

CREATE TABLE Questionaires
(QuestionaireID AUTOINCREMENT
,QuestionaireName TEXT(72) NOT NULL
,CONSTRAINT pk_Questionaires PRIMARY KEY (QuestionaireID)
)

CREATE TABLE QuestionaireSponsors
(QuestionaireSponsorID AUTOINCREMENT
,QuestionaireID LONG NOT NULL
,FacultyID LONG NOT NULL
,CONSTRAINT pk_QuestionaireSponsors PRIMARY KEY (StudentID)
,CONSTRAINT fk_QuestionaireSponsors_QuestionaireID FOREIGN KEY
(QuestionaireID)
REFERENCES Faculty
(QuestionaireID)
)

CREATE TABLE QuestionaireQuestions
(QuestionaireQuestionsID AUTOINCREMENT
,QuestionaireID LONG NOT NULL
,QuestionID LONG NOT NULL
,CONSTRAINT pk_QuestionaireQuestions PRIMARY KEY (QuestionaireID)
,CONSTRAINT fk_QuestionaireQuestions_Questionaires FOREIGN KEY
(QuestionaireID)
REFERENCES
Questionaires (QuestionaireID)
,CONSTRAINT fk_QuestionaireQuestions_Questions FOREIGN KEY
(QuestionID)
REFERENCES Questions
(QuestionID)
)

CREATE TABLE QuestionaireAnswers
(QuestionaireAnswersID AUTOINCREMENT
,StudentID LONG NOT NULL
,QuestionaireQuestionsID LONG NOT NULL
,StudentsAnswer TEXT(255) NOT NULL
,CONSTRAINT pk_QuestionaireAnswers PRIMARY KEY (QuestionaireID)
,CONSTRAINT fk_QuestionaireAnswers_Students FOREIGN KEY (StudentID)
REFERENCES Students
(StudentID)
,CONSTRAINT fk_QuestionaireAnswers_QuestionaireQuestionsID FOREIGN
KEY (QuestionaireQuestionsID)
REFERENCES
QuestionaireQuestions (QuestionaireQuestionsID)
)


There, that looks right (bear in mind I wrote that in about 30
minutes).

This looks amazing, but i am going to demonstrate how thick I am.

The Structure of the database has 1 questionnaire which has gone to 10
different schools. So 10 schools have had all the same questions. Also, the
sudents themselves are not identified, so the studentID will only be the
unique key to 1 questionnaire. there are about 150 questionnaires completed
for each school.

So I don't think we need a sponsors table, and i am slighlty puzzled by
reference to CONSTRAINT - what is this?

Am i right in thinking that there should be a table for questions which hold
all the questions for the questionnaire.

and then a table for answers which has the questionID as a foreign key; what
does REFERENCES mean?

Then we have a table for questionaires, which i think i understand, and a
table for questionnaire questions which has me more puzzled. I thought we had
questions already, are these different questions?

And then we have questionnaire answers which i thought we already had answers.

Are you suggesting that i have a separate table for Questions
and Answers, and questionnaire questions and answers. I would like to try
and keep this as simple as possible. If u don't mind bearing with me, and
explaining the terms u have used I would really appreciate it.

You can see why I am confused I hope.
"Chris2" wrote:


"KrazyRed" wrote in message
...
Ok, I have managed to get myself completely confused.

I have a table, called tblstudent, which has a series of questions
in it, as
in data entry for a questionnaire.

The name tblstudent implies that the table is about students, not
about questions.

That table sounds like it should be called QuestionaireAnswers.


I have read some books about relationships and the trouble i have is
that
several of the questions have the same choice of answer, for
example,
tblagreetype. ie. strongly agree, slighly agree, slighlty disagree
etc. When
i try to create the lookup table to link to this table, the
relationships
window looks all over the place.

The Tables would go like this:

CREATE TABLE Students
(StudentID AUTOINCREMENT
,NameFirst TEXT(72)
,NameMiddle TEXT(72)
,NameLast TEXT(72)
,CONSTRAINT pk_StudentID PRIMARY KEY (StudentID)
)

CREATE TABLE Faculty
(FacultyID AUTOINCREMENT
,NameFirst TEXT(72)
,NameMiddle TEXT(72)
,NameLast TEXT(72)
,CONSTRAINT pk_Faculty PRIMARY KEY (StudentID)
)

CREATE TABLE Questions
(QuestionID AUTOINCREMENT
,Question TEXT(255) NOT NULL
,CONSTRAINT pk_Questions PRIMARY KEY (QuestionID)
)

CREATE TABLE Answers
(AnswerID AUTOINCREMENT
,QuestionID LONG NOT NULL
,Answer TEXT(255) NOT NULL
,CONSTRAINT pk_Answers PRIMARY KEY (AnswerID)
,CONSTRAINT fk_Answers_Questions FOREIGN KEY (QuestionID)
REFERENCES Questions (QuestionID)
)

CREATE TABLE Questionaires
(QuestionaireID AUTOINCREMENT
,QuestionaireName TEXT(72) NOT NULL
,CONSTRAINT pk_Questionaires PRIMARY KEY (QuestionaireID)
)

CREATE TABLE QuestionaireSponsors
(QuestionaireSponsorID AUTOINCREMENT
,QuestionaireID LONG NOT NULL
,FacultyID LONG NOT NULL
,CONSTRAINT pk_QuestionaireSponsors PRIMARY KEY (StudentID)
,CONSTRAINT fk_QuestionaireSponsors_QuestionaireID FOREIGN KEY
(QuestionaireID)
REFERENCES Faculty
(QuestionaireID)
)

CREATE TABLE QuestionaireQuestions
(QuestionaireQuestionsID AUTOINCREMENT
,QuestionaireID LONG NOT NULL
,QuestionID LONG NOT NULL
,CONSTRAINT pk_QuestionaireQuestions PRIMARY KEY (QuestionaireID)
,CONSTRAINT fk_QuestionaireQuestions_Questionaires FOREIGN KEY
(QuestionaireID)
REFERENCES
Questionaires (QuestionaireID)
,CONSTRAINT fk_QuestionaireQuestions_Questions FOREIGN KEY
(QuestionID)
REFERENCES Questions
(QuestionID)
)

CREATE TABLE QuestionaireAnswers
(QuestionaireAnswersID AUTOINCREMENT
,StudentID LONG NOT NULL
,QuestionaireQuestionsID LONG NOT NULL
,StudentsAnswer TEXT(255) NOT NULL
,CONSTRAINT pk_QuestionaireAnswers PRIMARY KEY (QuestionaireID)
,CONSTRAINT fk_QuestionaireAnswers_Students FOREIGN KEY (StudentID)
REFERENCES Students
(StudentID)
,CONSTRAINT fk_QuestionaireAnswers_QuestionaireQuestionsID FOREIGN
KEY (QuestionaireQuestionsID)
REFERENCES
QuestionaireQuestions (QuestionaireQuestionsID)
)


There, that looks right (bear in mind I wrote that in about 30
minutes).

"KrazyRed" wrote in message
...
This looks amazing, but i am going to demonstrate how thick I am.

The Structure of the database has 1 questionnaire which has gone to
10
different schools. So 10 schools have had all the same questions.
Also, the
sudents themselves are not identified, so the studentID will only be
the
unique key to 1 questionnaire. there are about 150 questionnaires
completed
for each school.

Ah, and now I know far more than I did before.

The tables I tossed out were based on some assumptions on my part,
which turned out to be wrong.

It has the advantage of reusability. The database you're describing
above can only be used for that one questionaire, mine can be used for
unlimited numbers of questionaires, and supports question and answer
reuse between questionaires, etc. People building new questionaires
could have access to a "questionaire" builder Form which let them use
questions on file to help them in the build process.

Take the rest of my comments in this post in the correct context of
"I designed something with more capability than you wanted."



So I don't think we need a sponsors table, and i am slighlty puzzled
by
reference to CONSTRAINT - what is this?

The CONSTRAINT statement establishes a Primary Key or a Foreign Key,
and is a part of "Referential Integrity" (in MS Access; too bad Access
doesn't support CHECK . . .).

If you have Access 2K, open help, and type in "referential" in the
search box, and you should get an entry for "What is referential
integrity." (Other versions of Access may list it differently.)

In the table design page, you do the equivalent when you select a row
and click on the "key" button on the toolbar.

Access gets its user-friendliness by hiding most SQL (like the CREATE
TABLE statment, etc.) from you. Access also hampers learning by
hiding this information from you. It's a catch-22.

But when it comes to examples, do I write two pages of how to
mouse-click the way to a new table, or do I simply write the 3-8 line
CREATE TABLE statement? (Which you are free to cut and paste and use;
although it's to your benefit to study and learn it.)



Am i right in thinking that there should be a table for questions
which hold
all the questions for the questionnaire.

You can, and it's probably the best way.



and then a table for answers which has the questionID as a foreign
key; what
does REFERENCES mean?

CONSTRAINT primary-key-name PRIMARY KEY (column-name, . . . )

--The above establishes a "Primary Key". MS Access does this be
creating a unique not-null index on the column (or combination of
colunmns). "A Primary Key is the column or combination of columns
whose unique value *identifies* the row itself." Technically, in a
"real" "relational" database, no Table would ever be without a Primary
Key, although almost all actual relational database products allow you
to do it.


CONSTRAINT foreign-key-name FOREIGN KEY (column-name, . . . )
REFERENCES other-table
(column-from-other-table, . . .)

--The above establishes a "Foreign Key". MS Access does this be
creating a unique/non-unique (designer choice) not-null index on the
column (or combination of colunmns). When this happens, every time
you try to INSERT to this table, Access make sure that the value about
to go into the Foreign Key column is found somewhere in the
"REFERENCES" table and column. Every time you try to UPDATE or
DELETE, Access does the same thing.

--In the example table Answers, there is a Foreign Key to Questions.
If you try to INSERT a row into Answers that has a QuestionID of 2041,
and Access looks at Questions, and finds no value 2041 in the
QuestionID column there, it stops the INSERT (same for UPDATE and
DELETE). Trying to do any of these three things when you shouldn't
usually produces some sort of "key violation".

--Understanding Primary and Foreign Keys, what they mean, what they're
for, and where they're found, helps you to understand the source of
some error messages you will see. If you work solely with the Access
UI, all this "key" stuff is hidden behind the "Relationships" window.
But if you run an INSERT that says, "Could not INSERT 21 records
because of key violations," and you don't know what "keys" are,
there's going to be difficulty in diagnosing the problem.




Then we have a table for questionaires, which i think i understand,
and a
table for questionnaire questions which has me more puzzled. I
thought we had
questions already, are these different questions?

It can be done either way.

In my example:
I created a Table to hold "Questions" (things that could be asked).
I created a Table to hold "Answers" (valid responses to Questions).
I created Questionaires to hold Questionaires.
I created QuestionaireQuestions to hold a "group" of Questions for a
particular Questionaire.
I created QuestionaireAnswers to hold a student's responses to the
Questions on a Questionaire.

You require less than that, though, as you outlined above.


And then we have questionnaire answers which i thought we already
had answers.

Answers holds *valid* and true answers provided by the people making
the Questionaire.

Questionaire = "Cafeteria Food"
Question = "Do you like cafeteria chocolate pudding?" (QuestionID =
45)

QuestionaireQuestions: QuestionaireQuestionID (2005), "Cafeteria
Food", QuestionID (45)

Valid answers in Answers: Strongly Dislike, Dislike,
Take-it/Leave-it, Like, Strongly Like, Allergic, Never Tried.
(Appearing in drop-down combo box).

QuestionaireAnswers: QuestionaireAnswersID (51349), StudentID, 2005,
"Dislike"


Follow the chain of ID numbers.



Are you suggesting that i have a separate table for Questions
and Answers, and questionnaire questions and answers. I would like
to try
and keep this as simple as possible. If u don't mind bearing with
me, and
explaining the terms u have used I would really appreciate it.

You can make it as simple as you wish. Also bearing in mind that
you will only use it once, and when someone asks you to do it a second
time, a third time, and a forth time, each with new, empty copies of
the previous, and then someone comes along and asks you to compare the
information, track the response levels between them, etc.

Sorry . . . I tend to think in terms like that.


You can see why I am confused I hope.
"Chris2" wrote:


"KrazyRed" wrote in message
...
Ok, I have managed to get myself completely confused.

I have a table, called tblstudent, which has a series of
questions
in it, as
in data entry for a questionnaire.

The name tblstudent implies that the table is about students,
not
about questions.

That table sounds like it should be called QuestionaireAnswers.


I have read some books about relationships and the trouble i
have is
that
several of the questions have the same choice of answer, for
example,
tblagreetype. ie. strongly agree, slighly agree, slighlty
disagree
etc. When
i try to create the lookup table to link to this table, the
relationships
window looks all over the place.

The Tables would go like this:

CREATE TABLE Students
(StudentID AUTOINCREMENT
,NameFirst TEXT(72)
,NameMiddle TEXT(72)
,NameLast TEXT(72)
,CONSTRAINT pk_StudentID PRIMARY KEY (StudentID)
)

CREATE TABLE Faculty
(FacultyID AUTOINCREMENT
,NameFirst TEXT(72)
,NameMiddle TEXT(72)
,NameLast TEXT(72)
,CONSTRAINT pk_Faculty PRIMARY KEY (StudentID)
)

CREATE TABLE Questions
(QuestionID AUTOINCREMENT
,Question TEXT(255) NOT NULL
,CONSTRAINT pk_Questions PRIMARY KEY (QuestionID)
)

CREATE TABLE Answers
(AnswerID AUTOINCREMENT
,QuestionID LONG NOT NULL
,Answer TEXT(255) NOT NULL
,CONSTRAINT pk_Answers PRIMARY KEY (AnswerID)
,CONSTRAINT fk_Answers_Questions FOREIGN KEY (QuestionID)
REFERENCES Questions (QuestionID)
)

CREATE TABLE Questionaires
(QuestionaireID AUTOINCREMENT
,QuestionaireName TEXT(72) NOT NULL
,CONSTRAINT pk_Questionaires PRIMARY KEY (QuestionaireID)
)

CREATE TABLE QuestionaireSponsors
(QuestionaireSponsorID AUTOINCREMENT
,QuestionaireID LONG NOT NULL
,FacultyID LONG NOT NULL
,CONSTRAINT pk_QuestionaireSponsors PRIMARY KEY (StudentID)
,CONSTRAINT fk_QuestionaireSponsors_QuestionaireID FOREIGN KEY
(QuestionaireID)
REFERENCES
Faculty
(QuestionaireID)
)

CREATE TABLE QuestionaireQuestions
(QuestionaireQuestionsID AUTOINCREMENT
,QuestionaireID LONG NOT NULL
,QuestionID LONG NOT NULL
,CONSTRAINT pk_QuestionaireQuestions PRIMARY KEY (QuestionaireID)
,CONSTRAINT fk_QuestionaireQuestions_Questionaires FOREIGN KEY
(QuestionaireID)
REFERENCES
Questionaires (QuestionaireID)
,CONSTRAINT fk_QuestionaireQuestions_Questions FOREIGN KEY
(QuestionID)
REFERENCES
Questions
(QuestionID)
)

CREATE TABLE QuestionaireAnswers
(QuestionaireAnswersID AUTOINCREMENT
,StudentID LONG NOT NULL
,QuestionaireQuestionsID LONG NOT NULL
,StudentsAnswer TEXT(255) NOT NULL
,CONSTRAINT pk_QuestionaireAnswers PRIMARY KEY (QuestionaireID)
,CONSTRAINT fk_QuestionaireAnswers_Students FOREIGN KEY
(StudentID)
REFERENCES Students
(StudentID)
,CONSTRAINT fk_QuestionaireAnswers_QuestionaireQuestionsID
FOREIGN
KEY (QuestionaireQuestionsID)

REFERENCES
QuestionaireQuestions (QuestionaireQuestionsID)
)


There, that looks right (bear in mind I wrote that in about 30
minutes).