Compute Average (Aggregate Function) with Null Value in Query 2

[Tamrak]

Hello,

Need help on calculation on the average with null value. Let's say that the table has four fields, all numeric.
Field Names are: MonthA, MonthB, MonthC and MonthD. Let's say the data are; $400, (null), $500, $600.

How can I use a query wizard to construct a query that will return me an average of $500, instead of a blank? I created a calculate field called, "First4months".

Someone told me that Access won't accept the null value in any field, if there is a calculation involved.

Is there a way for me to run an average for these four months in Access without exporting to Excel and importing back?

Second question: Let's say, I have another four months, MonthE, MonthF, MonthG and MonthH. Data are: $500, $600, null, and $100. I name the calculated fields, "Middle4months." As you see, the average will be $400.

Is there anyway I can run the average for the entire eight months or I need to sum the "First4months" and "Middle4Months" to find an average?

I keep getting error about cannot use aggregate in another aggregate.

Do I need to construct two queries on the top of each other? If I do, am I required to use all the fields or just selected fields? Please help. Thank you again.

Anyone has the ideas, please let me know.

 

[RickSpr]

To get an average of several fields, some of which may be null, use this expression:

(Nz(MonthA, 0) + Nz(MonthB, 0) + Nz(MonthC, 0) + ...) / (n + IsNull(MonthA) + IsNull(MonthB) + IsNull(MonthC) + ...)

For 'n' substitute the number of fields you're averaging. The IsNull(MonthN) terms will return -1 for each null value. By adding them to 'n' you're deducting the number of null fields from the denominator, which gives you the correct average.

However, this won't work (it'll give you a division by zero error) if it's possible that all the fields are null. If so, I'd break down and write a custom function:

Public Function AvgOfMonths(ParamArray Months() As Variant) As Single
    Dim i As Long, sum As Single, count As Long
    For i = LBound(Months) To UBound(Months)
        sum = sum + Nz(Months(i))
        If Not IsNull(Months(i)) Then count = count + 1
    Next i
    If count = 0 Then
        AvgOfMonths = 0#
    Else
        AvgOfMonths = sum / count
    End If
End Function

Then, where you want the average, you can just code:
AvgOfMonths([MonthA], [MonthB], [MonthC], ...)
You can include as many months as you like. Rick Sprague

 

[Tamrak]

RickSpr,

Thank you for your post.

I forgot to mention that the MonthA, MonthB, MonthC, MonthD numbers are "Average." I would like to perform another "average" (these four months combined with some null values) on the top of this "average." The field "First4Months" is an "average" field which is the result of average MonthA, average MonthB, average MonthC and average MonthD. Some of the Monthly average returned a null value, such as monthB.


My additional question is:

If I use the same logics and generate the fields "first4months", "middle4months" and "last4months", can I run another "average"? I will name this field as "MonthlyAverage".

Thanks again.

 

[MichaelRed]

I'm probably in deep water here, but ...

averages of averages of averages of ... rapidly become meaningless in most situations. It is somewhere between hard and impossible to discern what your process is attempting to compute, but - IMHO - you should review the goal(s) and rethink/redesign the overall approach. Where I have seen this (averages of averages of averages of ... ) the real process needed to go back to the SOURCE data and generally do some aggregate and/or crosstab queries. Generally speaking (on average?) using intermediate results in cascade aggregate functions does NOT lead to the pronised (or is it the expected?) land.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[xollef]

Just had to add I managed to create this:
(Nz(MonthA, 0) + Nz(MonthB, 0) + Nz(MonthC, 0) + ...) / (n + IsNull(MonthA) + IsNull(MonthB) + IsNull(MonthC) + ...)
Without it giving #Num! error if all fields are empty. You don't have to write VBA to accomplish it.

This is my expression that calculates the values for 6 fields, and if every field is null then it leaves the field blank.

The expression:
=IIf(IsNull([Result1]);IIf(IsNull([Resultb1]);IIf(IsNull([Resultc1]);IIf(IsNull([Resultd1]);IIf(IsNull([Resulte1]);IIf(IsNull([Resultf1]);"";(Nz([Result1])+NZ([Resultb1])+Nz([Resultc1])+Nz([Resultd1])+Nz([Resulte1])+Nz([Resultf1]))/(6+IsNull([Result1])+IsNull([Resultb1])+IsNull([Resultc1])+IsNull([Resultd1])+IsNull([Resulte1])+IsNull([Resultf1])));(Nz([Result1])+NZ([Resultb1])+Nz([Resultc1])+Nz([Resultd1])+Nz([Resulte1])+Nz([Resultf1]))/(6+IsNull([Result1])+IsNull([Resultb1])+IsNull([Resultc1])+IsNull([Resultd1])+IsNull([Resulte1])+IsNull([Resultf1])));(Nz([Result1])+NZ([Resultb1])+Nz([Resultc1])+Nz([Resultd1])+Nz([Resulte1])+Nz([Resultf1]))/(6+IsNull([Result1])+IsNull([Resultb1])+IsNull([Resultc1])+IsNull([Resultd1])+IsNull([Resulte1])+IsNull([Resultf1])));(Nz([Result1])+NZ([Resultb1])+Nz([Resultc1])+Nz([Resultd1])+Nz([Resulte1])+Nz([Resultf1]))/(6+IsNull([Result1])+IsNull([Resultb1])+IsNull([Resultc1])+IsNull([Resultd1])+IsNull([Resulte1])+IsNull([Resultf1])));(Nz([Result1])+NZ([Resultb1])+Nz([Resultc1])+Nz([Resultd1])+Nz([Resulte1])+Nz([Resultf1]))/(6+IsNull([Result1])+IsNull([Resultb1])+IsNull([Resultc1])+IsNull([Resultd1])+IsNull([Resulte1])+IsNull([Resultf1])));(Nz([Result1])+NZ([Resultb1])+Nz([Resultc1])+Nz([Resultd1])+Nz([Resulte1])+Nz([Resultf1]))/(6+IsNull([Result1])+IsNull([Resultb1])+IsNull([Resultc1])+IsNull([Resultd1])+IsNull([Resulte1])+IsNull([Resultf1])))

Laughing maybe it's easier with VB, no solution is bad as long as they work eh?

 

[MichaelRed]

Public Function basAvgAry(ArayIn As Variant) As Variant

    'Michael Red    3/2/2002. Row Aggregate Function

    Dim Idx As Long
    Dim myval As Single
    Dim MyCnt As Long

    For Idx = 0 To UBound(ArayIn)
        myval = myval + Nx(ArayIn(Idx))
        MyCnt = MyCnt + Abs(Not IsNull(MyArayIn(Idx)))
    Next Idx

    If (MyCnt <> 0) Then
        basAvgAray = myval / MyCnt
    End If

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over