问题描述
我在报告中有2列，分别是qty_req和qty_issued.我需要在qty_issued列中找到平均值.问题是有时qty_req的对应值是0.我只需要对qty_req列否 0的行取qty_issued列的平均值.我该怎么办这个吗?
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?
推荐答案
如果您想在报表文本框的控制源中执行此操作，则可以利用以下事实: Avg()忽略Null值.
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.
因此，当qty_req<> 0时，在平均的值中包括qty_issued.否则，请使用Null代替qty_issued值.
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;
这篇关于Access女士:如何计算一列的平均值，而忽略另一列为0的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！