Average

[Templar333]

Hi All,

I'm working on a new query and this is where I'm stuck. The table that it draws info from has 5 fields that are actually employee wages for 5 consecutive years; 1997, 1998, 1999, 2000 and 2001. In the query, I'm trying to calculate an average for the 5 years BUT some of the fields are blank (not all employess have been there those 5 years). How do I calculate an average for these 5 years when some fields are null? Thanks.

Helm.

 

[lovalles]

you can use 2 command the iif for making a condision and the ISnull so you can make the condicion to calculate the average when the field not isnull(varible)

hope this help
lovalles

 

[cmmrfrds]

The avg function in your query should handle this for you. This is provided that an employee of less than five years you want the average for the time they actually worked.

select avg(employeeWages)


If you want the total wages / 5 then you need to force the null to a zero.

select avg(isnull(employeewages,0))

 

[CosmoKramer]

See if this link from Microsoft can help you.....

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q109364

 

[Templar333]

CosmoKramer,

I'd actually found a solution on Microsoft's website using RAvg in a module. It worked the first time I ran the query but after that, Access kept crashing. I exported it into a new database and it ran flawlessly agin the first time round and then came the crashes. I coudn't get it to run more than once and that's why I posted this thread hoping for another way to solve this. The link's:

http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q209839

cmmrfrds,

Pleasehelp eloborate. I'm new to this so even something simply could look awfully confusing Anyway, the fields are listed as 1997, 1998, 1999, 2000 and 2001. Are you suggesting that I pout the following into the query column:

select avg([1997], [1998], [1999], [2000], [2001])

lovalles, sorry mate, I didn't quite understand that.

Folks, thanks for being patient.

Helm.

 

[MichaelRed]

Public Function basAvgVal(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 4/9/2002
    'To return the AVERAGE or a series of values

    'Sample Usage:
    '? basAvgVal(1,12,3,5,78)
    ' 15.6

    Dim Idx As Integer
    Dim Jdx As Integer
    Dim MyAccum As Variant

    If (UBound(varMyVals) < 0) Then
        Exit Function
    End If

    For Idx = 0 To UBound(varMyVals())
        If (varMyVals(Idx) <> &quot;&quot; And Not IsNull(varMyVals(Idx))) Then
            MyAccum = varMyVals(Idx)
            Jdx = Jdx + 1
        End If
    Next Idx

    basAvgVal = MyAccum / Jdx

End Function

MichaelRed
m.red@att.net

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

 

[cmmrfrds]

I am sorry, I thought you were working with normalized data so I missed the fact there were 5 columns.

select (([1997] + [1998] + [1999] + [2000] + [2001]) / 5), etc.....

 

[Templar333]

Michael,

Can I safely assume that I copy and paste the above as a module and then type the following into the column in the query?

Result:basAvgVal ([1997],[1998],[1999],[2000],[2001])

Thanks a mil.

 

[MichaelRed]

Assuming the items in the brackets as field names, that is the way it is intended.

Safety is an entirely dirfferent issue.

MichaelRed
m.red@att.net

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