how best to do this:

[Blubba]

ok i have 2 tables: person, and answers.
person has personid;
answers has personid, questionid, answerid.

now i need a report to look like the following:

personid----ques1ans1----ques1ans2-----quest2ans1----ques2ans2, etc
1 0 1 1 0
2 1 0 0 1

so you can see the report shows all the possible answers for all the
questions, and shows a 1 if the person picked that response, and 0 if
they didn't.

can someone help me with this query? i can't change the database
design, and would really like to avoid looping through the person
records and doing sub queries.

 

[John Spencer (MVP)]

You should be able to use a crosstab query to do this.

If you are using the query grid to do this. Open a query and use your Answers
table as the source
Select Query: Crosstab from the menu
Enter the following in the grid.

Field: PersonID
Total: GroupBy
CrossTab: RowValue

Field: QuestionID
Total: GroupBy
Crosstab: ColumnValue

Field: AnswerID
Total: First
Crosstab: Value

 

[Mortar]

Sorry I don't follow.
From Access, I pick New Query and select Crosstab Query Wizard, but it
doesn't seem to allow the options you describe. Is this the query grid
you we're talking about? If not, how do I get to the query grid?

 

[Mortar]

ok this gives me the answerids but doesn't exactly do what I was
asking. Thanks for your help but please re-read my original message.

 

[Mortar]

nevermind, i think i got it. I use:

IIf([AnswerID]>0, '1', '0')

It works except it doesn't show 0 if not picked. But does show 1 if
picked. Weird but good enough.