AVERAGE

S

SteveL

Someone helped me with something like this a week ago and
I appreciate it but have to start over due my lack of
explaining the situation well.

I have one record with say values of 2, 5, 8, 0, & 0 in
five different fields. (Note that the last two are
zeros).

I know the average of the values (zeroes not counted) is
5.

How can I calculate that average in an additional
calculated field in a query that is already getting the
five values?

--Steve
 
N

Nikos Yannacopoulos

Steve,

[field1]+[field2]+[field3]+[field4]+[field5]/(IIf([field1]<>0,1,0)+IIf([fiel
d2]<>0,1,0)+IIf([field3]<>0,1,0)+IIf([field4]<>0,1,0)+IIf([field5]<>0,1,0))

HTH,
Nikos
 
M

Michel Walsh

Hi,


A database assumes, the developers that built the database tools assume that
you will work VERTICALLY, not horizontally. Let us do it.

SELECT pk, f1 As MyField FROM tableName
UNION ALL
SELECT pk, f2 FROM tableName
UNION ALL
SELECT pk, f3 FROM tableName
UNION ALL
SELECT pk, f4 FROM tableName
UNION ALL
SELECT pk, f5 FROM tableName



save it as Qu1.


Next, with that vertical representation:

SELECT pk, AVG(myField)
FROM Qu1
WHERE myFIeld <> 0
GROUP BY pk



where I assumed that pk is the primary key of the record.


Hoping it may help,
Vanderghast, Access MVP
 
Top