Calculating Avg with null values

[Maggie]

Hello all,
Query Average question: I'm attempting to average a handful of survey
result values (per record) in a query. For example, Bob answers my survey
and gives me a 4, 4, 3, 3, 4...this shoud average to 3.6. If everyone
answers all questions, no problem. The problem comes in when someone leaves
answers blank. I have a basic Avg set up to add the #s together and divide
by how many questions there are:

1xAvg:
Avg(([SurveyQuestion1Score]+[SurveyQuestion2Score]+[SurveyQuestion3Score]+[SurveyQuestion4Score]+[SurveyQuestion5Score])/5)

But when any of those values are blank, it doesn't calculate anything and
gives me a null answer...AND if I put zeros in the field, the averages are
then incorrect. I'm wondering if there is something I can replace that "/5"
with. Is there some sort of countnum statement that can replace that and
say: "add all these numbers together and divide by the count of values that
are not null"? Any thoughts would be greatly appreciated.
Thanks so much.

 

[John Spencer]

If you are calculating an average for a row, you can use the function below.
Copy it and paste it into a module and then get the average by using

fGetRowAverage(Avg(([SurveyQuestion1Score]+ [SurveyQuestion2Score]+
[SurveyQuestion3Score]+ [SurveyQuestion4Score]+ [SurveyQuestion5Score])

Another way would be to use a UNION query to fix the structure of your data so
you could use the avg function.
(Nz([SurveyQuestion1Score],0) + NZ([SurveyQuestion2Score],0)+
NZ([SurveyQuestion3Score],0) + NZ([SurveyQuestion4Score],0)+
Nz([SurveyQuestion5Score],0))/Abs(SurveyQuestion1Score is Not Null +
SurveyQuestion2Score is Not Null +SurveyQuestion3Score is Not Null
+SurveyQuestion4Score is Not Null +SurveyQuestion5Score is Not Null)

Then you could use Avg on that to get an average of the average, which may be
nonsensical.

Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call:
'myAvg = fRowAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL
' fRowSum and fRowCount can be used to overcome this limit

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then 'At least one number in the group of values
fRowAverage = dblSum / intElementCount

Else 'No number in the group of values
fRowAverage = Null
End If

End Function

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

Hello all,
Query Average question: I'm attempting to average a handful of survey
result values (per record) in a query. For example, Bob answers my survey
and gives me a 4, 4, 3, 3, 4...this shoud average to 3.6. If everyone
answers all questions, no problem. The problem comes in when someone leaves
answers blank. I have a basic Avg set up to add the #s together and divide
by how many questions there are:

1xAvg:
Avg(([SurveyQuestion1Score]+[SurveyQuestion2Score]+[SurveyQuestion3Score]+[SurveyQuestion4Score]+[SurveyQuestion5Score])/5)

But when any of those values are blank, it doesn't calculate anything and
gives me a null answer...AND if I put zeros in the field, the averages are
then incorrect. I'm wondering if there is something I can replace that "/5"
with. Is there some sort of countnum statement that can replace that and
say: "add all these numbers together and divide by the count of values that
are not null"? Any thoughts would be greatly appreciated.
Thanks so much.

 

[John Spencer]

Small Typo
fGetRowAverage([SurveyQuestion1Score]+ [SurveyQuestion2Score]+
[SurveyQuestion3Score]+ [SurveyQuestion4Score]+ [SurveyQuestion5Score])

And if you want the average of the average
Avg(fGetRowAverage(...))


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

If you are calculating an average for a row, you can use the function
below. Copy it and paste it into a module and then get the average by using

fGetRowAverage(Avg(([SurveyQuestion1Score]+ [SurveyQuestion2Score]+
[SurveyQuestion3Score]+ [SurveyQuestion4Score]+ [SurveyQuestion5Score])

Another way would be to use a UNION query to fix the structure of your
data so you could use the avg function.
(Nz([SurveyQuestion1Score],0) + NZ([SurveyQuestion2Score],0)+
NZ([SurveyQuestion3Score],0) + NZ([SurveyQuestion4Score],0)+
Nz([SurveyQuestion5Score],0))/Abs(SurveyQuestion1Score is Not Null +
SurveyQuestion2Score is Not Null +SurveyQuestion3Score is Not Null
+SurveyQuestion4Score is Not Null +SurveyQuestion5Score is Not Null)

Then you could use Avg on that to get an average of the average, which
may be nonsensical.

Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to
it.
'Sample call:
'myAvg = fRowAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL
' fRowSum and fRowCount can be used to overcome this limit

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then 'At least one number in the group of
values
fRowAverage = dblSum / intElementCount

Else 'No number in the group of values
fRowAverage = Null
End If

End Function

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

Hello all,
Query Average question: I'm attempting to average a handful of survey
result values (per record) in a query. For example, Bob answers my
survey and gives me a 4, 4, 3, 3, 4...this shoud average to 3.6. If
everyone answers all questions, no problem. The problem comes in when
someone leaves answers blank. I have a basic Avg set up to add the #s
together and divide by how many questions there are:
1xAvg:
Avg(([SurveyQuestion1Score]+[SurveyQuestion2Score]+[SurveyQuestion3Score]+[SurveyQuestion4Score]+[SurveyQuestion5Score])/5)

But when any of those values are blank, it doesn't calculate anything
and gives me a null answer...AND if I put zeros in the field, the
averages are then incorrect. I'm wondering if there is something I
can replace that "/5" with. Is there some sort of countnum statement
that can replace that and say: "add all these numbers together and
divide by the count of values that are not null"? Any thoughts would
be greatly appreciated.
Thanks so much.

 

[Maggie]

Ok, I pasted the code in a module and saved it down as basAvgWithoutNulls.
Then I pasted the function in a field in my query grid. It's telling me,
when I try to run it that it's "an undefined function". Am I missing a step?

Small Typo
fGetRowAverage([SurveyQuestion1Score]+ [SurveyQuestion2Score]+
[SurveyQuestion3Score]+ [SurveyQuestion4Score]+ [SurveyQuestion5Score])

And if you want the average of the average
Avg(fGetRowAverage(...))


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

If you are calculating an average for a row, you can use the function
below. Copy it and paste it into a module and then get the average by using

fGetRowAverage(Avg(([SurveyQuestion1Score]+ [SurveyQuestion2Score]+
[SurveyQuestion3Score]+ [SurveyQuestion4Score]+ [SurveyQuestion5Score])

Another way would be to use a UNION query to fix the structure of your
data so you could use the avg function.
(Nz([SurveyQuestion1Score],0) + NZ([SurveyQuestion2Score],0)+
NZ([SurveyQuestion3Score],0) + NZ([SurveyQuestion4Score],0)+
Nz([SurveyQuestion5Score],0))/Abs(SurveyQuestion1Score is Not Null +
SurveyQuestion2Score is Not Null +SurveyQuestion3Score is Not Null
+SurveyQuestion4Score is Not Null +SurveyQuestion5Score is Not Null)

Then you could use Avg on that to get an average of the average, which
may be nonsensical.

Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to
it.
'Sample call:
'myAvg = fRowAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL
' fRowSum and fRowCount can be used to overcome this limit

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then 'At least one number in the group of
values
fRowAverage = dblSum / intElementCount

Else 'No number in the group of values
fRowAverage = Null
End If

End Function

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

Hello all,
Query Average question: I'm attempting to average a handful of survey
result values (per record) in a query. For example, Bob answers my
survey and gives me a 4, 4, 3, 3, 4...this shoud average to 3.6. If
everyone answers all questions, no problem. The problem comes in when
someone leaves answers blank. I have a basic Avg set up to add the #s
together and divide by how many questions there are:
1xAvg:
Avg(([SurveyQuestion1Score]+[SurveyQuestion2Score]+[SurveyQuestion3Score]+[SurveyQuestion4Score]+[SurveyQuestion5Score])/5)

But when any of those values are blank, it doesn't calculate anything
and gives me a null answer...AND if I put zeros in the field, the
averages are then incorrect. I'm wondering if there is something I
can replace that "/5" with. Is there some sort of countnum statement
that can replace that and say: "add all these numbers together and
divide by the count of values that are not null"? Any thoughts would
be greatly appreciated.
Thanks so much.

 

[Maggie]

Hey John, I got PART of it....the code is fRowAverage instead of
fGetRowAverage. Fixed that, but the function seems to only be adding the
numbers together and not doing the division part. Thoughts?

Ok, I pasted the code in a module and saved it down as basAvgWithoutNulls.
Then I pasted the function in a field in my query grid. It's telling me,
when I try to run it that it's "an undefined function". Am I missing a step?

Small Typo
fGetRowAverage([SurveyQuestion1Score]+ [SurveyQuestion2Score]+
[SurveyQuestion3Score]+ [SurveyQuestion4Score]+ [SurveyQuestion5Score])

And if you want the average of the average
Avg(fGetRowAverage(...))


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

If you are calculating an average for a row, you can use the function
below. Copy it and paste it into a module and then get the average by using

fGetRowAverage(Avg(([SurveyQuestion1Score]+ [SurveyQuestion2Score]+
[SurveyQuestion3Score]+ [SurveyQuestion4Score]+ [SurveyQuestion5Score])

Another way would be to use a UNION query to fix the structure of your
data so you could use the avg function.
(Nz([SurveyQuestion1Score],0) + NZ([SurveyQuestion2Score],0)+
NZ([SurveyQuestion3Score],0) + NZ([SurveyQuestion4Score],0)+
Nz([SurveyQuestion5Score],0))/Abs(SurveyQuestion1Score is Not Null +
SurveyQuestion2Score is Not Null +SurveyQuestion3Score is Not Null
+SurveyQuestion4Score is Not Null +SurveyQuestion5Score is Not Null)

Then you could use Avg on that to get an average of the average, which
may be nonsensical.

Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to
it.
'Sample call:
'myAvg = fRowAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL
' fRowSum and fRowCount can be used to overcome this limit

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then 'At least one number in the group of
values
fRowAverage = dblSum / intElementCount

Else 'No number in the group of values
fRowAverage = Null
End If

End Function

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

Maggie wrote:
Hello all,
Query Average question: I'm attempting to average a handful of survey
result values (per record) in a query. For example, Bob answers my
survey and gives me a 4, 4, 3, 3, 4...this shoud average to 3.6. If
everyone answers all questions, no problem. The problem comes in when
someone leaves answers blank. I have a basic Avg set up to add the #s
together and divide by how many questions there are:
1xAvg:
Avg(([SurveyQuestion1Score]+[SurveyQuestion2Score]+[SurveyQuestion3Score]+[SurveyQuestion4Score]+[SurveyQuestion5Score])/5)

But when any of those values are blank, it doesn't calculate anything
and gives me a null answer...AND if I put zeros in the field, the
averages are then incorrect. I'm wondering if there is something I
can replace that "/5" with. Is there some sort of countnum statement
that can replace that and say: "add all these numbers together and
divide by the count of values that are not null"? Any thoughts would
be greatly appreciated.
Thanks so much.

 

[John Spencer]

It works for me (Access 2003).

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

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

Hey John, I got PART of it....the code is fRowAverage instead of
fGetRowAverage. Fixed that, but the function seems to only be adding the
numbers together and not doing the division part. Thoughts?

Ok, I pasted the code in a module and saved it down as basAvgWithoutNulls.
Then I pasted the function in a field in my query grid. It's telling me,
when I try to run it that it's "an undefined function". Am I missing a step?

Small Typo
fGetRowAverage([SurveyQuestion1Score]+ [SurveyQuestion2Score]+
[SurveyQuestion3Score]+ [SurveyQuestion4Score]+ [SurveyQuestion5Score])

And if you want the average of the average
Avg(fGetRowAverage(...))


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

John Spencer wrote:
If you are calculating an average for a row, you can use the function
below. Copy it and paste it into a module and then get the average by using

fGetRowAverage(Avg(([SurveyQuestion1Score]+ [SurveyQuestion2Score]+
[SurveyQuestion3Score]+ [SurveyQuestion4Score]+ [SurveyQuestion5Score])

Another way would be to use a UNION query to fix the structure of your
data so you could use the avg function.
(Nz([SurveyQuestion1Score],0) + NZ([SurveyQuestion2Score],0)+
NZ([SurveyQuestion3Score],0) + NZ([SurveyQuestion4Score],0)+
Nz([SurveyQuestion5Score],0))/Abs(SurveyQuestion1Score is Not Null +
SurveyQuestion2Score is Not Null +SurveyQuestion3Score is Not Null
+SurveyQuestion4Score is Not Null +SurveyQuestion5Score is Not Null)

Then you could use Avg on that to get an average of the average, which
may be nonsensical.

Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to
it.
'Sample call:
'myAvg = fRowAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL
' fRowSum and fRowCount can be used to overcome this limit

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then 'At least one number in the group of
values
fRowAverage = dblSum / intElementCount

Else 'No number in the group of values
fRowAverage = Null
End If

End Function

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

Maggie wrote:
Hello all,
Query Average question: I'm attempting to average a handful of survey
result values (per record) in a query. For example, Bob answers my
survey and gives me a 4, 4, 3, 3, 4...this shoud average to 3.6. If
everyone answers all questions, no problem. The problem comes in when
someone leaves answers blank. I have a basic Avg set up to add the #s
together and divide by how many questions there are:
1xAvg:
Avg(([SurveyQuestion1Score]+[SurveyQuestion2Score]+[SurveyQuestion3Score]+[SurveyQuestion4Score]+[SurveyQuestion5Score])/5)

But when any of those values are blank, it doesn't calculate anything
and gives me a null answer...AND if I put zeros in the field, the
averages are then incorrect. I'm wondering if there is something I
can replace that "/5" with. Is there some sort of countnum statement
that can replace that and say: "add all these numbers together and
divide by the count of values that are not null"? Any thoughts would
be greatly appreciated.
Thanks so much.

 

[Maggie]

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"));

It works for me (Access 2003).

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

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

Hey John, I got PART of it....the code is fRowAverage instead of
fGetRowAverage. Fixed that, but the function seems to only be adding the
numbers together and not doing the division part. Thoughts?

Ok, I pasted the code in a module and saved it down as basAvgWithoutNulls.
Then I pasted the function in a field in my query grid. It's telling me,
when I try to run it that it's "an undefined function". Am I missing a step?

:

Small Typo
fGetRowAverage([SurveyQuestion1Score]+ [SurveyQuestion2Score]+
[SurveyQuestion3Score]+ [SurveyQuestion4Score]+ [SurveyQuestion5Score])

And if you want the average of the average
Avg(fGetRowAverage(...))


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

John Spencer wrote:
If you are calculating an average for a row, you can use the function
below. Copy it and paste it into a module and then get the average by using

fGetRowAverage(Avg(([SurveyQuestion1Score]+ [SurveyQuestion2Score]+
[SurveyQuestion3Score]+ [SurveyQuestion4Score]+ [SurveyQuestion5Score])

Another way would be to use a UNION query to fix the structure of your
data so you could use the avg function.
(Nz([SurveyQuestion1Score],0) + NZ([SurveyQuestion2Score],0)+
NZ([SurveyQuestion3Score],0) + NZ([SurveyQuestion4Score],0)+
Nz([SurveyQuestion5Score],0))/Abs(SurveyQuestion1Score is Not Null +
SurveyQuestion2Score is Not Null +SurveyQuestion3Score is Not Null
+SurveyQuestion4Score is Not Null +SurveyQuestion5Score is Not Null)

Then you could use Avg on that to get an average of the average, which
may be nonsensical.

Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to
it.
'Sample call:
'myAvg = fRowAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'Ignores values that cannot be treated as numbers.
'
' Max of 29 arguments can be passed to a function in Access SQL
' fRowSum and fRowCount can be used to overcome this limit

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then 'At least one number in the group of
values
fRowAverage = dblSum / intElementCount

Else 'No number in the group of values
fRowAverage = Null
End If

End Function

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

Maggie wrote:
Hello all,
Query Average question: I'm attempting to average a handful of survey
result values (per record) in a query. For example, Bob answers my
survey and gives me a 4, 4, 3, 3, 4...this shoud average to 3.6. If
everyone answers all questions, no problem. The problem comes in when
someone leaves answers blank. I have a basic Avg set up to add the #s
together and divide by how many questions there are:
1xAvg:
Avg(([SurveyQuestion1Score]+[SurveyQuestion2Score]+[SurveyQuestion3Score]+[SurveyQuestion4Score]+[SurveyQuestion5Score])/5)

But when any of those values are blank, it doesn't calculate anything
and gives me a null answer...AND if I put zeros in the field, the
averages are then incorrect. I'm wondering if there is something I
can replace that "/5" with. Is there some sort of countnum statement
that can replace that and say: "add all these numbers together and
divide by the count of values that are not null"? Any thoughts would
be greatly appreciated.
Thanks so much.

 

[John Spencer]

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
'====================================================

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"));

It works for me (Access 2003).

Please copy and post the SQL of your query.

 

[Maggie]

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

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
'====================================================

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"));

It works for me (Access 2003).

Please copy and post the SQL of your query.

 

[Maggie]

Crap...one more thing. Thoughts on how to make this resulting value have 2
decimal places? The properties aren't seeing this as a number so there's no
option to choose format or decimal places.

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
'====================================================

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"));

It works for me (Access 2003).

Please copy and post the SQL of your query.

 

[John W. Vinson]

Crap...one more thing. Thoughts on how to make this resulting value have 2
decimal places? The properties aren't seeing this as a number so there's no
option to choose format or decimal places.

Just change

Public Function fRowAverage(ParamArray Values())


to

Public Function fRowAverage(ParamArray Values()) As Double

and (either in the function or in the query) throw in a Round(<expression>, 2)

 

[John Spencer]

IF you are SURE that you will always get back a non-null value then you
can wrap the whole thing in a conversion function. As long as at least
on of the fields has a value you will get back a number result.

You should be able to do this.

CDbl(fRowAverage(....))

John Vinson's suggestion will work as long as at least one of the
arguments has a number value. Otherwise you will get an error.


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

Crap...one more thing. Thoughts on how to make this resulting value have 2
decimal places? The properties aren't seeing this as a number so there's no
option to choose format or decimal places.

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
'====================================================

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"));


:

It works for me (Access 2003).

Please copy and post the SQL of your query.

 

[Maggie]

ok, I put the Cdbl() around the expressions in the query. It's working in 5
of the 6 expressions...but for this one, it just isn't happening. Any
thoughts?

2xAvg:
CDbl(fRowAverage([SurveyQuestion6Score],[SurveyQuestion7Score],[SurveyQuestion8Score]))

IF you are SURE that you will always get back a non-null value then you
can wrap the whole thing in a conversion function. As long as at least
on of the fields has a value you will get back a number result.

You should be able to do this.

CDbl(fRowAverage(....))

John Vinson's suggestion will work as long as at least one of the
arguments has a number value. Otherwise you will get an error.


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

Crap...one more thing. Thoughts on how to make this resulting value have 2
decimal places? The properties aren't seeing this as a number so there's no
option to choose format or decimal places.

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"));


:

It works for me (Access 2003).

Please copy and post the SQL of your query.

 

[John Spencer]

Is there any record where all three of those fields is blank? IF so that will
cause an error.

Try

IIF(IsNull(SurveyQuestion6Score) and IsNull(SurveyQuestion7Score) and
IsNull(SurveyQuestion8Score),Null,
CDbl(fRowAverage([SurveyQuestion6Score],[SurveyQuestion7Score],[SurveyQuestion8Score])))

Or if you would accept ZERO as the average in this case, then you could modify
the function to return zero in cases where all the arguments were null. The
modification would be at the bottom


Else 'No number in the group of values
'fRowAverage = Null should be replaced with the next line
fRowAverage = 0
End If

By the way, "it just isn't happening" is not very descriptive of what the
problem is. I guessed that you are getting an error message, but you could be
getting the wrong results or the computer could be bursting in smoke or the
application could be crashing.

Please try to be a bit more descriptive of what the problem is. And if you
are getting an error message and there is text associated with the error, try
to post the text (or a synopsis of the text).

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

ok, I put the Cdbl() around the expressions in the query. It's working in 5
of the 6 expressions...but for this one, it just isn't happening. Any
thoughts?

2xAvg:
CDbl(fRowAverage([SurveyQuestion6Score],[SurveyQuestion7Score],[SurveyQuestion8Score]))

IF you are SURE that you will always get back a non-null value then you
can wrap the whole thing in a conversion function. As long as at least
on of the fields has a value you will get back a number result.

You should be able to do this.

CDbl(fRowAverage(....))

John Vinson's suggestion will work as long as at least one of the
arguments has a number value. Otherwise you will get an error.


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

 

[Maggie]

that fixes the "#Error"s that show up when all 3 are blank, but it's the
decimals that are the problem for this one expression. the Cdbl solves the
2-decimal places for 5 of my 6 expressions. The one pasted below, though, is
not converting to 2 decimal places.
Maybe I should say screw it and just set them in a rpt...

Is there any record where all three of those fields is blank? IF so that will
cause an error.

Try

IIF(IsNull(SurveyQuestion6Score) and IsNull(SurveyQuestion7Score) and
IsNull(SurveyQuestion8Score),Null,
CDbl(fRowAverage([SurveyQuestion6Score],[SurveyQuestion7Score],[SurveyQuestion8Score])))

Or if you would accept ZERO as the average in this case, then you could modify
the function to return zero in cases where all the arguments were null. The
modification would be at the bottom


Else 'No number in the group of values
'fRowAverage = Null should be replaced with the next line
fRowAverage = 0
End If

By the way, "it just isn't happening" is not very descriptive of what the
problem is. I guessed that you are getting an error message, but you could be
getting the wrong results or the computer could be bursting in smoke or the
application could be crashing.

Please try to be a bit more descriptive of what the problem is. And if you
are getting an error message and there is text associated with the error, try
to post the text (or a synopsis of the text).

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

ok, I put the Cdbl() around the expressions in the query. It's working in 5
of the 6 expressions...but for this one, it just isn't happening. Any
thoughts?

2xAvg:
CDbl(fRowAverage([SurveyQuestion6Score],[SurveyQuestion7Score],[SurveyQuestion8Score]))

IF you are SURE that you will always get back a non-null value then you
can wrap the whole thing in a conversion function. As long as at least
on of the fields has a value you will get back a number result.

You should be able to do this.

CDbl(fRowAverage(....))

John Vinson's suggestion will work as long as at least one of the
arguments has a number value. Otherwise you will get an error.


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