Ms Access: How can I take the average of a column

Question

I have 2 columns, qty_req and qty_issued in a report. I need to find the average of the values in the qty_issued column. The problem is that sometimes the corresponding value of qty_req is 0. I need to take the average of the qty_issued column for only the rows where qty_req is NOT 0. How do I do this?

Spun off from my other question here: MS Access: How can I average a list of quantities on a report where the quantities are not zero?

Answer 1

You would set the criteria in the query to not look at the qty_req that is 0:

SELECT Avg(MyTable.qty_issued) AS Avg_Issued
FROM MyTable
GROUP BY MyTable.qty_req
HAVING MyTable.qty_req <> 0;

Answer 2

If you want to do that in the Control Source of a text box on your report, you can take advantage of the fact that Avg() ignores Null values.

So, when qty_req <> 0, include qty_issued among the values which are averaged. Otherwise use Null instead of the qty_issued value.

=Avg(IIf(qty_req <> 0, qty_issued, Null))

If you want to do it in a query instead ...

SELECT Avg(IIf(qty_req <> 0, qty_issued, Null)) FROM YourTable;