Re: Calculating Avg with null values

John Spencer...I don't know who you are, but you are the bomb! Thank you so
much! Works like a charm!

"John Spencer" wrote:

DOH!! Dumb mistake on my part, replaces the + with a comma.

SELECT tblScheduledClass.[Date of Class], tblInstructors.Instructor,
[tblAttendees&Scores].[Class Attended],
[tblAttendees&Scores].SurveyQuestion1Score,
[tblAttendees&Scores].SurveyQuestion2Score,
[tblAttendees&Scores].SurveyQuestion3Score,
[tblAttendees&Scores].SurveyQuestion4Score,
[tblAttendees&Scores].SurveyQuestion5Score,
[tblAttendees&Scores].SurveyQuestion6Score,
[tblAttendees&Scores].SurveyQuestion7Score,
[tblAttendees&Scores].SurveyQuestion8Score,
fRowAverage([SurveyQuestion1Score], [SurveyQuestion2Score],
[SurveyQuestion3Score], [SurveyQuestion4Score], [SurveyQuestion5Score])
AS 1xAvg
FROM (tblInstructors INNER JOIN tblScheduledClass ON tblInstructors.ID =
tblScheduledClass.tblInstructors_ID) INNER JOIN [tblAttendees&Scores] ON
tblScheduledClass.[Date of Class] = [tblAttendees&Scores].[Date of Class]
WHERE ((([tblAttendees&Scores].[Class Attended])="InterAction for Data
Stewards and Marketing Users"));

My error and I do apologize.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Maggie wrote:
Yikes... :) Here it is. Keep in mind I'm calculating one average for
questions 1-5 and will be creating a second one for questions 6-8 (which I
haven't written yet). Thought I'd get the first one down, then the rest will
be easy enough to copy.

SELECT tblScheduledClass.[Date of Class], tblInstructors.Instructor,
[tblAttendees&Scores].[Class Attended],
[tblAttendees&Scores].SurveyQuestion1Score,
[tblAttendees&Scores].SurveyQuestion2Score,
[tblAttendees&Scores].SurveyQuestion3Score,
[tblAttendees&Scores].SurveyQuestion4Score,
[tblAttendees&Scores].SurveyQuestion5Score,
[tblAttendees&Scores].SurveyQuestion6Score,
[tblAttendees&Scores].SurveyQuestion7Score,
[tblAttendees&Scores].SurveyQuestion8Score,
fRowAverage([SurveyQuestion1Score]+[SurveyQuestion2Score]+[SurveyQuestion3Score]+[SurveyQuestion4Score]+[SurveyQuestion5Score]) AS 1xAvg
FROM (tblInstructors INNER JOIN tblScheduledClass ON tblInstructors.ID =
tblScheduledClass.tblInstructors_ID) INNER JOIN [tblAttendees&Scores] ON
tblScheduledClass.[Date of Class] = [tblAttendees&Scores].[Date of Class]
WHERE ((([tblAttendees&Scores].[Class Attended])="InterAction for Data
Stewards and Marketing Users"));


"John Spencer" wrote:

It works for me (Access 2003).

Please copy and post the SQL of your query.

.

Relevant Pages

  • Re: Calculating Avg with null values
    ... "John Spencer" wrote: ... FROM (tblInstructors INNER JOIN tblScheduledClass ON tblInstructors.ID = ... Stewards and Marketing Users")); ...
    (microsoft.public.access.queries)
  • Re: Calculating Avg with null values
    ... Stewards and Marketing Users")); ... FROM (tblInstructors INNER JOIN tblScheduledClass ON tblInstructors.ID = tblScheduledClass.tblInstructors_ID) INNER JOIN ON ...
    (microsoft.public.access.queries)
  • Re: Calculating Avg with null values
    ... "John Spencer" wrote: ... FROM (tblInstructors INNER JOIN tblScheduledClass ON tblInstructors.ID = tblScheduledClass.tblInstructors_ID) INNER JOIN ON ...
    (microsoft.public.access.queries)