Repost - Grouping query

[Red via AccessMonster.com]

After much research on this site, have come to the following conclusions on
my problem; Frst I need to create a "count " query but have tried to figure
out which fields to add count. Second, I need to create a "Sum" query to
give me the answer to my question. The fields I need to include in my query
is Date, Question number, Answer value and Pass value.

Currently I get a result that looks like

VisitDate QuestionNumber Value PassValue
2/6/2005 001 1 1
5/2/2005 001 1 1
12/15/2005 001 1 1
12/30/2005 001 n/a 1
2/1/2006 001 1 1
2/3/2006 001 1 1
2/6/2005 002 1 1
5/2/2005 002 1 1
12/15/2005 002 1 1
12/30/2005 002 n/a 1
2/1/2006 002 1 1
2/3/2006 002 1 1
Etc...

What I'm trying to accomplish is a result that looks something like this;

Total of Question 1 Total of Value of Question 1 Total
Pass Value for Question 1
12 14
12

Etc...

Hope this make sense to someone :>/


I have inclosed my SQL for you to look at.


SELECT TblVisit.VisitDate, TblQuestions.QuestionNumber, TblAnswer.Value,
TblQuestions.PassValue
FROM (TblVisit RIGHT JOIN (TblAnswer RIGHT JOIN TblRecords ON TblAnswer.
AnswerID = TblRecords.AnswerID) ON TblVisit.VisitID = TblRecords.VisitID)
LEFT JOIN TblQuestions ON TblAnswer.QuestionID = TblQuestions.QuestionID
GROUP BY TblVisit.VisitDate, TblQuestions.QuestionNumber, TblAnswer.Value,
TblQuestions.PassValue
ORDER BY TblQuestions.QuestionNumber;


Is this at all possible? I want to export the results of this new query to
excel. Any help would be greatly appreciated. Hope I have enclosed enough
information.

Thanks in advance for any advice given.

 

[Michel Walsh]

Hi,


COUNT(*) will count the number of records, COUNT(Value) will count the
number of records where the value is not null (not n/a).



SELECT QuestionNumber, COUNT(*), COUNT(Value)
FROM mytable
GROUP BY QuestionNumber


seems to be what you need.


Hoping it may help,
Vanderghast, Access MVP

 

[Red via AccessMonster.com]

Hi Michel,
Thanks for youe reply. Your SQL worked exactly as I wanted it to for
QuestionNumber. I've been trying to manipulate it to include the rest of the
fields with no luck. Any suggestions on how to incorporate the rest of my
required fields Value and PassValue into this SQL?

Many Thanks for your help.

Red

Hi,

COUNT(*) will count the number of records, COUNT(Value) will count the
number of records where the value is not null (not n/a).

SELECT QuestionNumber, COUNT(*), COUNT(Value)
FROM mytable
GROUP BY QuestionNumber

seems to be what you need.

Hoping it may help,
Vanderghast, Access MVP

After much research on this site, have come to the following conclusions
on

[quoted text clipped - 52 lines]

Thanks in advance for any advice given.

 

[Michel Walsh]

Hi,




Sometimes, if appropriate, add the extra fields in the GROUP BY, then, you
can also add them in the SELECT clause.

Another alternative, if the other fields all have the same value, or if just
some value from a record from the group is appropriate, aggregate the other
fields with LAST:

===============
SELECT QuestionNumber, COUNT(*), COUNT(Value), LAST(otherField1),
LAST(otherField2)

FROM mytable

GROUP BY QuestionNumber
================




Hoping it may help,
Vanderghast, Access MVP

Hi Michel,
Thanks for youe reply. Your SQL worked exactly as I wanted it to for
QuestionNumber. I've been trying to manipulate it to include the rest of
the
fields with no luck. Any suggestions on how to incorporate the rest of my
required fields Value and PassValue into this SQL?

Many Thanks for your help.

Red

Hi,

COUNT(*) will count the number of records, COUNT(Value) will count the
number of records where the value is not null (not n/a).

SELECT QuestionNumber, COUNT(*), COUNT(Value)
FROM mytable
GROUP BY QuestionNumber

seems to be what you need.

Hoping it may help,
Vanderghast, Access MVP

After much research on this site, have come to the following
conclusions
on

[quoted text clipped - 52 lines]

Thanks in advance for any advice given.

 

[Red via AccessMonster.com]

Hi Michel,
Got the query to work and wanted to post a thank you for your help. FYI I
am posting my SQL to see how I did it and hopefully you cant find any
problems with it :>/ Thanks again.

SELECT [Copy Of qryRecordsQandAnswers].QuestionNumber, [Copy Of
qryRecordsQandAnswers].CountOfQuestionNumber, [Copy Of qryRecordsQandAnswers].
SumOfValue, [Copy Of qryRecordsQandAnswers].SumOfPassValue, Sum([SumOfValue]/
[SumOfPassValue])*100 AS exp
FROM [Copy Of qryRecordsQandAnswers]
GROUP BY [Copy Of qryRecordsQandAnswers].QuestionNumber, [Copy Of
qryRecordsQandAnswers].CountOfQuestionNumber, [Copy Of qryRecordsQandAnswers].
SumOfValue, [Copy Of qryRecordsQandAnswers].SumOfPassValue;


VR,

Red

Hi,

Sometimes, if appropriate, add the extra fields in the GROUP BY, then, you
can also add them in the SELECT clause.

Another alternative, if the other fields all have the same value, or if just
some value from a record from the group is appropriate, aggregate the other
fields with LAST:

===============
SELECT QuestionNumber, COUNT(*), COUNT(Value), LAST(otherField1),
LAST(otherField2)

FROM mytable

GROUP BY QuestionNumber
================

Hoping it may help,
Vanderghast, Access MVP

Hi Michel,
Thanks for youe reply. Your SQL worked exactly as I wanted it to for

[quoted text clipped - 27 lines]