averaging specific data

[pat67]

Hi, I want to average data but I need to exclude the highest value. in
other words I have 18,20, 25,32, 10. I want to average 10, 18, 20 and
25 eliminating 32. Any ideas?

 

[vanderghast]

SELECT AVG(myfield)
FROM somewhere
WHERE myField < DMAX("myField", "somewhere")


Someone may replace DMAX with a sub query.

Note that if the max, say 32, occurs twice or more, all its occurrence are
removed. If you only want to remove one occurrence, keeping the other
instances, use:


SELECT (SUM(myfield) - MAX(myfield) ) / ( COUNT(*) - 1)
FROM somewhere



Vanderghast, Access MVP

 

[pat67]

SELECT AVG(myfield)
FROM somewhere
WHERE myField < DMAX("myField", "somewhere")

Someone may replace DMAX with a sub query.

Note that if the max, say 32, occurs twice or more, all its occurrence are
removed. If you only want to remove one occurrence, keeping the other
instances, use:

SELECT (SUM(myfield) - MAX(myfield) ) / ( COUNT(*) - 1)
FROM somewhere

Vanderghast, Access MVP






- Show quoted text -

Thanks. I actually came up with the same exact query you have second.