averaging specific data

  • Thread starter Thread starter pat67
  • Start date Start date
P

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?
 
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
 
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 -
Click to expand...

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