Re: Mixed up with Relationships..help!

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Chris2 (rainofsteel.NOTVALID_at_GETRIDOF.luminousrain.com)
Date: 01/25/05 

Date: Mon, 24 Jan 2005 18:55:35 -0800

"KrazyRed" <KrazyRed@discussions.microsoft.com> wrote in message
news:95DA600E-7DE2-4FF7-8CA2-5286EB91B609@microsoft.com...
> 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).