Query calculation help...

[jacqueline]

I have 2 english prereq grades...I want to add them together and divide by 2
for the average.
BUT if the person has 1 score and 1 zero (meaning he only did 1 prereq
English course)...I would want the 1 score to appear in the calculated field.

Thanks!

 

[JL]

Hi jacqueline,
It would really help if I know how the table was setup.
If the table "Prereq" has StudentID, Grades, then the query is as simple as
not include the 0 as part of your calculation.

Like:
Select StudentID, Grades from Prereq where Grades <> 0;
-- or --
Select StudentID, Grades from Prereq where Grades > 0; -- since there
should not be negative scores.

Then you can use this query to do your calculation.

Like:
select StudentID, Avg(Grades) from Prereq where Grades <> 0
GROUP BY StudentID, Grades
HAVING Grades<>0;

Hope this helps.

 

[jacqueline]

The 2 english grades are part of a much larger table called Education...for
the 2 english fields...EnglishGrade1 and EnglishGrade2 there is either a
grade or a 0 in the field.
The query I am trying to run has many other fields...but instead of listing
the 2 separate english grades in the report...they want the average of the 2
grades in the report...and if the student only did 1 prereq English
course...the report should just show that grade.

 

[JL]

Hi jacqueline,
So both grades are on the same record.
Well, there are many way to do it. I do not know how familiar are you with
Access. I would write a function to do my calculation as shown below. It
will return blank if there is no Grades at all. If there is just one grade,
then it will return that one. If there is 2 grades, then tit will average
them.

Query:
select StudentID, GradeAvg(EnglishGrade1, EnglishGrade2) from Education .....

Module: Open your Modules Tab. Click New. Then a VBA Editor will show up.
Copy and Paste the Code below to the Module code screen.

Function GradeAvg(EnglishGrade1 As Variant, EnglishGrade2 As Variant) As
Variant
Dim Tmp_Avg As Variant
Dim Tmp_Ctr As Integer

Tmp_Avg = ""
Tmp_Ctr = 0
If Nz(EnglishGrade1) > 0 Then
Tmp_Ctr = Tmp_Ctr + 1
End If
If Nz(EnglishGrade2) > 0 Then
Tmp_Ctr = Tmp_Ctr + 1
End If
If Tmp_Ctr > 0 Then
Tmp_Avg = (CDbl(Nz(EnglishGrade1)) + CDbl(Nz(EnglishGrade2))) /
Tmp_Ctr
End If
GradeAvg = Tmp_Avg
End Function

Hope this helps.

 

[jacqueline]

Is there a simple way to just use an IIF statement in the query to get this
average...and if there is only 1 grade and 1 zero to not divide by 2??

I am just trying to include the English grade average in a report.

 

[John Spencer (MVP)]

How about the following

AvgGrade: ABS((EnglishGrade1 + EnglishGrade2)/(EnglishGrade1>0 + EnglishGrade2>0))

That will work for every case that one or both grades has a value other than
zero. You should get an error if the one or the other is blank or both of them
are zero.

More robust, accounting for nulls, zeroes in both fields, etc. That assumes that
I got all the parentheses matched up correctly. This is UNTESTED code.

AvgGrade: IIF(CDbl(NZ(EnglishGrade1,0)) + CDbl(NZ(EnglishGrade2,0)) = 0, 0,
ABS((CDbl(NZ(EnglishGrade1,0)) + CDbl(NZ(EnglishGrade2,0)))/
(CDbl(NZ(EnglishGrade1,0))>0 + CDbl(NZ(EnglishGrade2,0))>0)))

 

[jacqueline]

Thanks John...this part of yur post is what I'm wondering about...
"You should get an error if the one or the other is blank or both of them
are zero."

The 1st grade will definitely be there...but some people may not have
written a second one...therefore no grade (but defaults to 0)
If a person has the 1st English prereq but not the 2nd one...I would still
need to see the 1st grade in the report...
If there are 2 grades...I want the average of the 2....

 

[John Spencer (MVP)]

As long as at least one grade is greater than zero, you should be ok. And both
grades must have a value, even if one of them is only zero.

The following combinations should work with no problem with the first variation.

10,0
10,1
0,10

This will give an error
0,0

And you won't get correct results with
10, <blank>
<blank>,10

Try the formula and see if it works. If it doesn't then post back and tell us
why it doesn't work. Wrong values? No Values? Error messages?

 

[jacqueline]

The 2 English Grade fields are in the dbo_Education table (as englishgrade
and englishgrade2)
All tables have User_ID as a foreign key.
User_ID is the primary key in the dbo_Users table.

So I'm selecting several fields for the report out of several different
tables...and the avg English grade is just 1 of those.