Dan,
This would be a lot easier if your table were normalized. I'll assume these
are students, and that these responses are to a test. Same technique would
apply if this were a survey. Then I would normalize the table something
like:
StudentID TestID QuestionID Response
Bob 1 1 Y
Bob 1 2 N
Bob 1 3 Y
....
Bob 1 1 N
Bob 1 2 N
....
Joe 1 1 N
Joe 1 2 Y
Joe 1 3 N
Then you could create a crosstab query with StudentID, TestID, and
QuestionID as your row headings, Response as your column heading, and then
use Count(StudentID) as the value for your crosstab.
You did not indicate whether you had a test number to go with this data, so
you might want to through that out, or you might want to put a testDate in
there instead.
You can transform your table into this format with a query that looks like:
SELECT StudentID, 1 as TestID, 1 as QuestionID, Q1 as Response
FROM yourTable
UNION ALL
SELECT StudentID, 1 as TestID, 2 as QuestionID, Q2 as Response
FROM yourTable
UNION ALL
SELECT StudentID, 1 as TestID, 3 as QuestionID, Q3 as Response
FROM yourTable
UNION ALL
SELECT StudentID, 1 as TestID, 4 as QuestionID, Q4 as Response
FROM yourTable
UNION ALL
SELECT StudentID, 1 as TestID, 5 as QuestionID, Q5 as Response
FROM yourTable
Save this query, then use it as the source of a maketable query to get your
data into a normalized table.
HTH
Dale
