i have several calculations in my queries that include several variables. each of these variables has a potential to be scored as a 1, 0 or 99. a 99 is a missing value and i want to exclude it in my calculation. any ideas? will criteria just limit my total in the calc. as opposed to limiting the value of each specific variable in the calculation. any suggestions are very much appreciated.

If you use selection criteria of -
<> 99
Your query will exclude the rows that contain that answer so they will not be counted or used in the calculation at all.

the thing is, i still want it to use that id, for example, i just want it to drop 99 from the calculation. but not necessarily drop the entire id if 99 is found anywhere in their data. i don't know if there is an easy answer to this problem.

thanks.

The usual value for unknown fields is null. Using 99 is what has caused the problem. Change your table definition so that the fields in question do not have a default value and are not required. Then run update queries to replace all the existing 99's with null.

Access' aggregate functions will ignore nulls so the average of 1,0,null,1 is 2 divided by 3 rather than 2 divided by 4.