Re: Multiple choice exam scoring, help needed.

[Bjørn Munch]

I think you are making this too difficult for yourself by having individual columns for each answer. That forces you to treat each of them separately instead of having a general case, and it also can't be reused for other exams with other number of answers than 140.

I would suggest you try a more general solution like this, where q_id runs from 1 to 140 in your case:

kt2_test (student_id char(20), q_id int, answer char(1))
kt2_answer(q_id int, correct char(1))

That means 140 times as many records in the first table and one per question in the other, but you can then write much more general queries.

You might simplify the student_id to an int via another mapping table if size/performance is any issue.

The rest is left as an exercise to the reader. :-)