Totals Query Help

[John V]

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.

 

[Duane Hookom]

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.

 

[van_slanzar]

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!

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

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.

 

[Duane Hookom]

Follow-up in proper news group...

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!

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

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.