Totals Query?

  • Thread starter Thread starter James
  • Start date Start date
Ok I have now set up my main table as it is yours... How
do I now get it to act as in your Example?

Also how would you like teh other tables structured?

If you give me a brief example as I did of the fields and
everything I will do this and then we can move on and put
all this behind us.

Many Thanks Again

James
 
Yes I did... But it confused me slightly... Its just as it
was on about things here and there I didnt know where I
was up to or where anythig was going :-S

Thanks

James
 
You can build a query like this:

SELECT tblQuestions.Question, tblQuestionResponses.RespondentID,
tblQuestionResponses.QuestionID, tblQuestionResponses.Gender,
tblQuestionResponses.Answer
FROM tblQuestions LEFT JOIN tblQuestionResponses ON tblQuestions.QuestionID
= tblQuestionResponses.QuestionID;

Any questions not answered will show up a blank next to the question.
You'll need to provide the RepondentID, but the query will fill in
tblQuestionResonses.QuestionID as soon as you start to enter anything.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Ok...

Based on what we know..

How would I build my query to create a percentage? based
on teh data pulled up from my three combo boxes??

Many Thanks

James
 
SELECT tblQuestionResponses.QuestionID, tblQuestionResponses.Gender,
Count(tblQuestionResponses.RespondentID) As TotalAnswers,
(tblQuestionResponses.RespondentID)/(Select Count(RespondentID) FROM
tblQuestionResponses As QR2 WHERE QR2.QuestionID =
[Forms]![MyForm]![QuestionSelected]) As [Percentage]
FROM tblQuestionResponses
WHERE tblQuestionResponses.QuestionID = [Forms]![MyForm]![QuestionSelected]
AND tblQuestionResponses.Gender = [Forms]![MyForm]![GenderSelected]
AND tblQuestionResponces.Answer = [Forms]![MyForm]![AnswerSelected]
GROUP BY tblQuestionResponses.QuestionID, tblQuestionResponses.Gender

You can also do it for all questions like this:

SELECT tblQuestionResponses.QuestionID, tblQuestionResponses.Gender,
Count(tblQuestionResponses.RespondentID) As TotalAnswers,
(tblQuestionResponses.RespondentID)/(Select Count(RespondentID) FROM
tblQuestionResponses As QR2 WHERE QR2.QuestionID =
tblQuestionResponses.QuestionID) As [Percentage]
FROM tblQuestionResponses
WHERE tblQuestionResponses.Gender = [Forms]![MyForm]![GenderSelected]
AND tblQuestionResponces.Answer = [Forms]![MyForm]![AnswerSelected]
GROUP BY tblQuestionResponses.QuestionID, tblQuestionResponses.Gender

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top