Totals Query Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have responses from a 13-item student evaluation questionnaire, three of
which are demographic questions (e.g. Major, GPA, and Expected Grade).

I wish to average items [N09] and [N10] by Course and by each possible
demographic response (3 for Major [C11], 5 for GPA [C12], and 5 for Grade
[C13]. So at the end of this fresh hell I will have 26 statistics, all of
which will be printed within the detail section of a report.

I suppose I will have a Totals Query GROUPED BY Course, together with 26
expressions.

My first attempt looks as follows (for one of the 26 expressions only):

SELECT qryConvertLtrToNumber.Course,
IIf(Abs(Sum(IIf([N11]=4,1,0)))=0,Null,Abs(Sum(IIf([N11]=4,[N09],0)))/Abs(Sum(IIf([N11]=4,1,0)))) AS P01B
FROM tblRawData INNER JOIN qryConvertLtrToNumber ON tblRawData.Course =
qryConvertLtrToNumber.Course
GROUP BY qryConvertLtrToNumber.Course;

I can confirm this produces mathematically accurate results. But I just have
to believe there is a simpler way to produce the same result. Any help or
suggestions appreciated.
 
My apologies for not specifying. The Data table contains AgencyNumber to link
the two tables together. And also what I want the user to be able to specify
which month he/she wants to look at. Thanks in advance again!

Duane Hookom said:
The better solution is to have a better table structure. If you can't
normalize your table, you might consider using a union query.
There is a fairly normalized solution (At Your Survey) with some statistics
at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


John V said:
I have responses from a 13-item student evaluation questionnaire, three of
which are demographic questions (e.g. Major, GPA, and Expected Grade).

I wish to average items [N09] and [N10] by Course and by each possible
demographic response (3 for Major [C11], 5 for GPA [C12], and 5 for Grade
[C13]. So at the end of this fresh hell I will have 26 statistics, all of
which will be printed within the detail section of a report.

I suppose I will have a Totals Query GROUPED BY Course, together with 26
expressions.

My first attempt looks as follows (for one of the 26 expressions only):

SELECT qryConvertLtrToNumber.Course,
IIf(Abs(Sum(IIf([N11]=4,1,0)))=0,Null,Abs(Sum(IIf([N11]=4,[N09],0)))/Abs(Sum(IIf([N11]=4,1,0))))
AS P01B
FROM tblRawData INNER JOIN qryConvertLtrToNumber ON tblRawData.Course =
qryConvertLtrToNumber.Course
GROUP BY qryConvertLtrToNumber.Course;

I can confirm this produces mathematically accurate results. But I just
have
to believe there is a simpler way to produce the same result. Any help or
suggestions appreciated.
Click to expand...
Click to expand...
 
Follow-up in proper news group...

van_slanzar said:
My apologies for not specifying. The Data table contains AgencyNumber to
link
the two tables together. And also what I want the user to be able to
specify
which month he/she wants to look at. Thanks in advance again!

Duane Hookom said:
The better solution is to have a better table structure. If you can't
normalize your table, you might consider using a union query.
There is a fairly normalized solution (At Your Survey) with some
statistics
at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


John V said:
I have responses from a 13-item student evaluation questionnaire, three
of
which are demographic questions (e.g. Major, GPA, and Expected Grade).

I wish to average items [N09] and [N10] by Course and by each possible
demographic response (3 for Major [C11], 5 for GPA [C12], and 5 for
Grade
[C13]. So at the end of this fresh hell I will have 26 statistics, all
of
which will be printed within the detail section of a report.

I suppose I will have a Totals Query GROUPED BY Course, together with
26
expressions.

My first attempt looks as follows (for one of the 26 expressions only):

SELECT qryConvertLtrToNumber.Course,
IIf(Abs(Sum(IIf([N11]=4,1,0)))=0,Null,Abs(Sum(IIf([N11]=4,[N09],0)))/Abs(Sum(IIf([N11]=4,1,0))))
AS P01B
FROM tblRawData INNER JOIN qryConvertLtrToNumber ON tblRawData.Course =
qryConvertLtrToNumber.Course
GROUP BY qryConvertLtrToNumber.Course;

I can confirm this produces mathematically accurate results. But I just
have
to believe there is a simpler way to produce the same result. Any help
or
suggestions appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top