Averaging a field excluding zero's

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a "survey" database. I need to average the responce to each question or field in the database, here's the part I am having trouble with, I need to exclude any answers of 0, all answers are on scale of 1-10 "0" being used for answers of N/A

When I set the Criteria field in the query to Not "0" or <= "0". I am getting a error message "cannot have aggregate function in WHERE clause (Avg([tblquestionnaire]![Wuestion1])<="0").

Obvious newbie here, do most of my work in design mode. Any help would be greatly appreciated.
 
in design-view, add Wuestion1 again
change "Total" (row under Table) to WHERE
(don't show up)
criteria : >0
(excludes all records with value <=0 before averaging)
-----Original Message-----
I have a "survey" database. I need to average the
Click to expand...
responce to each question or field in the database, here's
the part I am having trouble with, I need to exclude any
answers of 0, all answers are on scale of 1-10 "0" being
used for answers of N/A.
When I set the Criteria field in the query to Not "0" or
Click to expand...
<= "0". I am getting a error message "cannot have
aggregate function in WHERE clause (Avg([tblquestionnaire]!
[Wuestion1])<="0").
Obvious newbie here, do most of my work in design mode.
Click to expand...
Any help would be greatly appreciated.
 
That worked but it completely excluded any record with a "0" in question 1 field. I need to exclude only the 0 from the field average ( or survey average) not the entire record, as some quesitons may be a "0 or N/A" and others have values that need to be included in the query.

Thanks for the help though this has me moving in the right direction, any further advice that may help?
 
Thanks for the assistance.. I decided to allow nulls in these fields elimanting the 0 problem.
 
Back
Top