Crosstab problem

[5lights]

CRv11
I want a Crosstab for each Person Identified in a survey giving a summary of how they were evaluated on 10 questions.
This should be easy but....
The 'Person' is identified by a multiple select in Question1.
The table looks like this:
RESPONSES(Question, Answer, Respondent)
Ques[Tab]Ans[Tab]Resp
1[Tab][Tab]Bob[Tab]123
2[Tab][Tab]A[Tab][Tab]123
3[Tab][Tab]D[Tab][Tab]123
1[Tab][Tab]Sue[Tab]124
2[Tab][Tab]B[Tab][Tab]124
3[Tab][Tab]C[Tab][Tab]124
1[Tab][Tab]Bob[Tab]125
1[Tab][Tab]Sue[Tab]125
2[Tab][Tab]B[Tab][Tab]125
3[Tab][Tab]C[Tab][Tab]125
For this Bob's summary should be
[Tab][Tab]A[Tab]B[Tab]C[Tab]D
2[Tab][Tab]1[Tab]1[Tab]0[Tab]0
3[Tab][Tab]0[Tab]0[Tab]1[Tab]1

I guess I could do a seperate subreport for each possible 'Person'(30+)...there must be an easier way

 

[synapsevampire]

Why does the Ans contain the persons name? Horrible table design there... And what is RESP, the respondents ID, assuming that is Bob, or? And is there a table with respondent to name, or?

I'd correct the data source using a UNION ALL or some such to create an intelligent data source.

You'd need to supply the database type though, this should be included with every post here.

-k

 

[5lights]

Its a MS Access db.
Yes, I agree completely....lousy db design.
But, it is the survey scanning output I have to deal with.

Question 1 is Who...
& the answer 'Bob' is the 'Person' I want to summarize
Yes, RESP is the RESPONDENT ID.

So Bob's summary has to cover Questions 2-10 for every Respondent who answered 'Bob' for Q'1
and Sue's the same
but both will count the Answers from RESP 125

 

[5lights]

I may look into making my own MS SQL db & importing the data to more appropriate tables....
any suggestions?
I'm thinking just pulling Answer & RespondentID where QuestionId=1 to a seperate "Employee" table should do the trick