Setting average across

[mugziegumz via AccessMonster.com]

I have 3 columns in my table: Attitude, Communication, Performance. I want
my last column to total across (NOT DOWN) the 3 ratings. Is this possible?
I'm brand new with access!
Shannon

 

[John Spencer (MVP)]

It's possible with a calculated field

Field: AverageValue:Abs( NZ(Attitude,0) + Nz(Communication,0) +
Nz(Performance,0) / (Performance is not null + Communicationis not Null +
Performance is not Null))

Another option is to use a custom VBA function to calculate the average.

If you want the SUM (total)
Field: TotalValue: NZ(Attitude,0) + Nz(Communication,0) + Nz(Performance,0)

If you know that the three fields will always have a value then the average is
a lot easier

Field: AverageValue: (Attitude + Communication + Performance) /3

Field: TotalValue: Attitude + Communication + Performance

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[mugziegumz via AccessMonster.com]

Not always will there be a rating. Not until employee is complete with
project. Where do I put these formulas? You have written below "Field" but I
don't know what that is being new with access...

 

[John Spencer]

You will need a query to show the data. So the Field: was to indicate
that you should use an empty Field block in a query to enter the data.

This will still generate an error (divide by zero error) if all three
fields are empty (null).

So, until you know how to build a query and use it, you cannot use this
solution. Since you posted the question in the queries newsgroup I
assumed that you at least knew how to build a query.

Good Luck

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================