Updating Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables in a database designed to store exam questions. One table
has the text of the question and answer choices, along with other textual
fields identifying the question. The other table contains testing data
related to this question.

My problem arises from the fact that questions get added and deleted quite
frequently, and I need the two tables to always carry information for the
same questions. Thus, if I delete a question from one table I need it to be
deleted on the other, and the same way for adding questions. Can anyone
explain how to do this?

Thank you very much for your help!

Joe
 
Thanks, Duane. I can see where our tables differ. Your "tblQuestions" table
has subdatasheets attached and all of the information flows through this
table. I have to say, however, that I cannot get an idea about applying this
to my own program by simply looking at yours. Is there somewhere else I can
read about normalizing already existing tables? Anything other than the
Access Help pages, that is?

Thanks again,

Joe
 
OK... I do add records for questions. Each record in my main table,
"tblExam," represents one question. Each question has many fields: there is
a field for the question id, one for the question text, and one each for each
answer choice. Then there are a number of fields that categorize the
question -- fields such as "subject," "specification," "author," "topic,"
etc...

Wholly separate from this table is "tblExamData," which I've tried to make
relate to "tblExam." "tblExamData" contains records that match the question
id (which i've tried to relate), and then performance data for each question.

My problem, then, is two-fold: I want to be able to make these tables
relatable so that they are updating. I also want to be able to store data
for multiple years for each question. I suppose this is similar to having
multiple orders for one customer. I've read the information that Duane sent
in the last post and it's been helpful... but I would still appreciate
another nudge in the right direction.

Thanks again,

Joe
 
Your table still is not fully normalized if you have "one each for each
answer choice". I would not have fields for "subject", "specification",...
These would be separate records.
 
Duane, I really appreciate your patience with me on this. We are in
agreement: my table is not properly normalized.

Let me try to explain my database more clearly:

What color is the sky?
a. blue
b. green
c. yellow
d. orange

If this is my exam question, I currently fit all of the information
pertaining to it into one record in a table:

ID: 1
Question: What color is the sky?
Choice A: blue
Choice B: green
Choice C: yellow
Choice D: orange
Correct Answer: A
Author: Joe Leo
Subject: Nature
Topic: Observable Surroundings
Status: Current
Image Attached? (Y/N): No
05 Difficulty: .85
05 Discrimination: .25
04 Difficulty: .82
04 Discrimination: .35

Now, each question has these same identifiers (and a few more, but this is
the gist of it). The subject and topic fields are used to help group
questions so that I can generate exams by randomly selecting a certain number
of questions from each group. Thus, there are many repeats when all of the
data is in one table. I understand that these should probably be separate
tables. But it isn't totally clear to me how I should proceed. Should every
item that repeats itself become its own table? And how do I incorporate
testing data (difficulty and discrimination) across multiple years?

Once again, your help in making this procedure more lucid is greatly
appreciated.

Thanks,

Joe
 
I would remove the choices and place them in a separate table:
tblAnswerChoices
=============
QuestionID link to tblExamQuestions.ID
Choice text like "blue", "Green", "Yellow",...

You could add fields for sort order and correct answer.

I would probably pull out the 05 and 04 fields and place them in another
related table.

This is how "At Your Survey" is built.
 
Back
Top