Help with getting average in a query?

[Guest]

I am creating a report that needs the average of prerequisite english courses
taken by med school applicants.

There are fields: EngGrade1 and EngGrade2
Most applicants have done 2 of them...but some students may have them done
but didn't record a grade for 1 or the other...or possibly both.
If that field isn't filled in with a score out of 100 it gets a default
value of 0.

So there may be records like these:
1. EngGrade1 = 75 EngGrade2 = 80
2. EngGrade1 = 0 EngGrade2 = 80
3. EngGrade1 = 75 EngGrade2 = 0
4. EngGrade1 = 0 EngGrade2 = 0

How can I make an expression within a query that will give me the average of
these 2 fields correctly.
For example...Student 2 above...I want that avg to just reflect the 1
score...80, not 80+0 /2 = 40

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT StudentID,
EngGrade1,
EngGrade2,
(EngGrade1 + EngGrade2) /
IIf(EngGrade1=0 OR EngGrade2=0,1,2) As EngAvg

FROM table_name

WHERE <criteria>

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQfq4roechKqOuFEgEQIoVgCgj9NSCwILd9DbZ6x4PgeN1eTlpxkAnR1o
9YCSIXmzugsq8lyh/zyrV/+8
=eGHl
-----END PGP SIGNATURE-----