Need help w/ query on multiple tables

Hi everyone,

I'm building a little survey application, and i'm currently trying to build reports out from the answers from users. I need to use the following 5 tables:

tblQuestions:
-questionId
-questionName

tblAnswers:
-answerId
-answerName

tblSurveyQuestions:
-surveyId
-questionId
-questionSequence

tblQuestionAnswers
-questionId
-answerId
-answerSequence

tblUsersAnswers:
-userId
-surveyId
-questionId
-answerId

Basically, for a certain surveyId, I need the question Ids & names, answer Id & names and total count for each answer. The results might look like:

Q1 - Are you happy?
A1) Yes - 5
A2) No - 4
A3) Maybe - 0

The following query gives the proper question & answer names, but the answer count is giving out wrong numbers:

SQLsurveyQuestion="SELECT A.questionId, A.questionSequence, B.questionName, E.answerId, D.answerName, Count(E.answerId) AS TotalAnswers
FROM (tblSurveyQuestions AS A
INNER JOIN ((tblQuestions AS B
INNER JOIN tblQuestionsAnswers AS C ON B.questionId=C.questionId)
INNER JOIN tblAnswers AS D ON C.answerId=D.answerId) ON A.questionId=B.questionId)
LEFT JOIN tblUsersAnswers AS E ON D.answerId=E.answerId
WHERE A.surveyId=1
GROUP BY D.answerId, A.questionId, B.questionName, D.answerName, A.questionSequence, C.answerSequence
ORDER BY A.questionSequence, C.answerSequence"

There's probably a mistake somewhere in the JOINS but i can't figure it out. Any suggestions or advice would be greatly appreciated.

This is for an Access DB.

Thanks!

Philippe

Do you really need Answers/QuestionsAnswers/UserAnswers, same goes for Questions/SurveyQuestions. Just seems to me you have too many tables, explain what they're used for por favor.

The reason behind the need to have these tables is that each question can be used in multiple surveys, and each answer can be used in multiple questions. And the table tblUsersAnswers is because we want to keep track of exactly who answered what, as opposed to keeping only the total count per answer (which would be a whole lot easier to manage).

Hope this helps clarify

what's wrong w/the query? It runs, no?

I'm assuming they are allowed to answer more than 1 answer per question???...if not then why are you wanting to count the answer? Are you getting "1" for each count?

...or are you trying to "sum" the value of the answer?

Ie, if "yes" then return 5, if "no" then return 4, if "maybe" then return 0...is that what you're looking for?