Count of records from multiple columns 1

[dbero]

[highlight #FCE94F][/highlight]

Hello,

I have a table that has test results in it. Each question is a column, and each row is the demographic/date information.
It basically looks like this.
Name TestDate Question 1 Qestion 2 Question 3
Tom 3/15/2013 Correct Correct Fail
Dick 4/1/2013 Fail Correct Fail
Harry 5/2/2013 Correct Correct Correct

What I need to do is to calculate the total pass/fail ratio for the entire test. So i need a query that adds up count of Fails from every question, and also adds up the count of corrects from every question. I then of course want to calculate the score. Is there an easy way to do this with a minimal number of queries? I don't want to have a pass and fail query for each question.
thank you!!!!

 

[dhookom]

You could begin by creating a normalizing union query with SQL something like:

SELECT [Name], TestDate, Question1 as Answer, 1 as Question
FROM tblUnnormalized
UNION ALL
SELECT [Name], TestDate, Question2, 2
FROM tblUnnormalized
UNION ALL
SELECT [Name], TestDate, Question3, 3
FROM tblUnnormalized;

You can then do a simple totals query.

Duane
Hook'D on Access
MS Access MVP

 

[dbero]

Thank you very much. i will give that a try tomorrow!