Answer Key Totals 1

[dodge20]

I have 2 tables
entries
answer_key

Students submit their answers to a database through a form over the web. I want to put the correct answers in the answer key and then total the number correct for each student. So I will need a query to show the count of correct answers for each student. I assume I will have to match up each field in the key with each field in the entry table, but don't know how to go about this.

The entries table consists of a studentid and a bunch of question fields and a total_correct field. The answer_key table just consists of the question fields.

Example:
Key
1. A
2. B
3. C
4. D

Student1
1. A
2. B
3. B
4. D
Total Correct 3



Dodge20

 

[PHV]

Something like this ?
SELECT StudentID, Count(*) As TotalCorrect
FROM tblStudents S INNER JOIN tblCorrectAnswers A
ON S.Key = A.Key AND S.Answer = A.Answer
GROUP BY StudentID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dodge20]

How do I make this so it computes it for all answers. I Can't join all the fields together, because then it won't display anything if 1 answer is incorrect.
Am I making sense?

Right now my table is, ans1, ans2, ans3, ans4, and so on. So if I join the student table with the answer table on all the answer fields, I won't get any results.

Dodge20

 

[PHV]

Do you know how to make a normalization union query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dodge20]

Not sure what you mean, but I am guessing it is something like this:

SELECT StudentID, Count(*) As TotalCorrect
FROM tblStudents S INNER JOIN tblCorrectAnswers A
ON S.Key = A.Key AND S.Answer = A.Answer
GROUP BY StudentID
UNION ALL SELECT Ans1 FROM tblStudents WHERE A.ans1 = S.ans1
UNION ALL SELECT Ans1 FROM tblStudents WHERE A.ans2 = S.ans2
?????

Dodge20

 

[PHV]

Say your table is StudentID, ans1, ans2, ans3, ans4.
The normalization query is something like this:
SELECT StudentID, 1 As [Key], ans1 As Answer FROM tblStudents
UNION SELECT StudentID, 2, ans2 FROM tblStudents
UNION SELECT StudentID, 3, ans3 FROM tblStudents
UNION SELECT StudentID, 4, ans4 FROM tblStudents

This give you a virtual table you can easyly join to the correct answers one to do the stats.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dodge20]

Thanks I think that will work.

Dodge20