CODEWITHSUNDEEP
ms-accessvba
Supernova
Supernova

Reputation: 88

Find average of one field for null values of another field

I have two text boxes to calculate sum and average. One text box is used to calculate sum of null values in a field for which I use this function:

=Sum(IIf(IsNull([field1])=True,1,0))

This gives correct answer, but for another text box I need to calculate average of field2 only for records that corresponds to null values of field1. I tried using DAvg() function which give back the average of whole field2 which is not what I want. Can anyone please help?

Upvotes: 1

Views: 419

Answers (1)

Erik A
Erik A

Reputation: 32642

You can just use a DAvg with a where condition:

=DAvg("Field2", "MyTable", "Field1 Is Null")

An alternate approach, that is more optimized for aggregate queries, would be the following:

Sum(Iif(IsNull(Field1), Field2, 0))/Count(Iif(IsNull(Field1), 1, Null))

Since the sum divided by the amount is equal to the average, and Access doesn't count Null. Note that this alternate approach might return an incorrect result if there are rows where Field1 and Field2 are both Null

Upvotes: 1

Related Questions