sql rows to columns in Access or is my schema wrong?

[Greynoise]

I have some tables set up in the following format:-

User Table:
UserID, Name, Email
1,bob, bob@bob.com
2,john, john@john.com

Questions table:
QuestionID, Question, Value
1, question1, 100
2, question2, 200
3, question3, 300
4, question4, 400

Answer table:
(the question is 'will you do something?', 3 answers are possible yes, no or already doing it. I'm using 1,0,2 in the real db as a numeric field)
AnswerID, UserID, QuestionID, Answer
1, 1, 1, yes
2, 1, 2, no
3, 1, 3, no
4, 1, 4, already doing it
1, 2, 1, no
2, 2, 2, yes
3, 2, 3, yes
4, 2, 4, yes

I need to add up all the yes values so:-
total yes = 1000

I've got that working using sum and inner joins but I cannot work out how to output the following which is also required:-

UserID, name, email, , question1, question2, question3, question4
1, bob, bob@bob.com, yes, no, no, already doing it
2, john, john@ john.com, no, yes, yes, yes

I have an asp page which does workusing a for loop, however ther client has asked for a view to be created in Access for this. The number of questions per user will always remain the same - 4 in my example.

Is my schema wrong or is there a way to achive this in Access with sql?

Regards

Louis

 

[Greynoise]

One thing I forgot to mention is that new questions may be added at a later date, I dont mind if the sql needs to be modified when this happens.

Lou