Multiple choice answer tracking and exam tracking

[Dominic]

Hello all.
I am creating a database to track exam results (pass / fail marks) but also
to track the actual questions as well. The questions are not in the database
though. This is what I have so far:
Tables:
Exam subject, exam number, exam information.
Exam information is the main table and the others are linked to it (via
selectable dropdown menus).

Content of "Exam Information" table:
ID, date, name, subject, exam #, # of times sat, mark, 1,2 - 50.
I want to be able to see which questions are constantly being gotten wrong,
so I have set up the table to be either a "1" or a "zero(0)" for the
"question" field.

QUERIES:
The "exam info" query is based on the table, less the ID, name, date, # of
times sat. This is where I am getting stuck....well, one of the places. I
have tried various things to get the info I need but nothing seems to work. I
need to get the number of people who have gotten a particular question WRONG
so that I can have a look at it to see if the question is bad, etc. How would
I go about doing that?
I tried to have te "question" fields as yes/no but that didn't work. When i
change the field to a "1" or "0" field, the report from the query just brings
up a total of people who got it wrong (as required) but always gives me a
100% indication, instead of a 50% indication if 2 out of 4 people got the
answer wrong.

Can anyone help?

Thanks in advance.
Regards,
Dominic

 

[Allen Browne]

One of the most fundamental rules of database design is not to use repeating
fields, such as Question1, Question2, ... Question50. Since one exam can
have multiple questions, you need a related table to hold the questions.

So your Exam table with have fields such as:
ExamID AutoNumber primary key
SubjectID Number relates to your table of subjects
ExamDate Date/Time when this exam was held
Then the ExamQuestion table will have fields like this:
ExamID Number which exam this question is in
QuestionNo Number 1 for question 1, 2 for question 2, ...
QuestionText Memo what the question was
MarksPossible Number how many marks q. is worth

That defines the exam itself. There's a whole other field in trying to
design a database to track:
a) students
b) courses
c) enrolments in course
d) the subjects/modules that make up a course (required + elective)
e) entry by a student to a subject
f) the assessments for the subject
g) attempts by a student an an assessment, when the attempts were received,
when they were submitted for grading (and possibly re-grading), and the
student's results.
h) resits (exams) and resubmissions (essays)
and so on.

 

[Dominic]

Allen,
Thank you for explaining. I think I might not have adequately conveyed the
intention of the database. It will not actually have any questions to answer
(that's why I didn't think of having a separate table). The record in the
"exam info" table is to track each "sitting" of a particular exam. The actual
answer is not important, but tracking which ones were answered wrong, is. I
hope this better explains what I want to do.

Going by your example, here is the basic content / layout / makeup of my
table as it stands:
Field Name Date Type Descr
ID Autonumber
ExamDate Date of exam Date
Candidate Text Name
ExamSubject Text Subject
Paper# Text Examination#
Examsitting Number # of this particular attempt
Exammark Number Actual Exam Mark
1 Number Correct or wrong
2 Number Correct or wrong
3 Number Correct or wrong
#'s continue thru to 50.

What I want to do is be able to have a report of how many people sat the
particular exam (the actual exam subjects and paper #'s will be separated
using a query..because there are about 20 different exams) and have how many
times each question was answered wrong.

I initially had the 1 thru 50 as yes/no but this didn't give the required
output. I then changed them to number fields, hoping that a count would yeild
better results. I am getting the number of people who are getting the
questions wrong, but not the percentage of how many people who actually took
the exam (eg: 6% of the people who took the exam got #2 wrong...there is no
need to change that question, but 95% of the people who took the exam got #25
wrong....we have to seriously look at the wording of that question).

I hope this gives better insight into what I am trying to achieve. If your
answer is still the same, please point me in the correct direction. If it
would be easier, I would email you the file or put it onto a server for you
to have a look at.

Thanks in advance,

Dominic

 

[Allen Browne]

IMHO you still need to use related tables.

 

[Dominic]

Allen,
Thanks for getting back so quickly. OK, I am barking up the wrong tree. So
you suggest I use the format in your previous message. I will do up an "Exam"
table and an "ExamQuestion" table. I will input all of the questions, the
correct answer, etc, as per your example below. How would I proceed from
there? Don't worry, I am not asking you to do it for me, just steer me in the
right direction and I will (try to) do it. Your valuable input helped me
create a reservations database a couple of years ago, so your "humble
opinion" is just fine with me!!!!!
Best regards,
Dominic.
BTW, once it's finished, I can send it to you so you can see how your input
has helped.

 

[Allen Browne]

You don't have to put in the question text, marks, and correct answer. But
you do need a related table that gives you a *record* for each question the
student attempts, instead of a *field* for each question in one big wide
table.

You say you want to track:
a) the students
b) the exams
c) each time a student attempts an exam (resits possible?)
d) whether the student gave the correct answer to each question in the exam.

You could do that with the 4 tables above.

Say we name (c) as tblAttempt. It will have fields:
- AttemptID AutoNumber primary key
- ExamID what exam they sat (relates to tblExam.ExamID)
- StudentID who sat the exam (relates to tblStudent.StudentID)
- AttemptDate Date/Time when this student made this attempt at this
exam.

Now table (d) contains a record for each question in the exam, for each
student.
- AttemptID Number (relates to tblAttempt.AttemptID)
- Question Number the question number in the exam
- Mark Number 1 for right; 0 for wrong.
So, if 10 students sat 2 exams, each with 20 questions, you have 10 * 2 * 20
records in this table.

For entering exam marks, you use a main form bound to tblAttempt, and a
subform in continuous view for the questions and marks (one per row.) The
subform automatically shows you just the records for the attempt in the main
form.