KrazyRed said:
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.
