Average calculation in query >0

[lschuh]

I am trying to write syntax in a query to do the following. Calculate an average on 12 fields (months) where the number to be averaged is greater than 0. I don't know how to combine the greater than 0 with average numbers above zero to get an accurate average. I have changed the validation rule in the database to >0 I have went through the database and added 0 to all the null fields. What happens is that /12 or the months in a year is not accurately giving me an average of the months with numbers. What we do is have blood drawn on all employees every 3 months or 4 times a year. Any person with a lead level above 15 has a blood draw every month therefore not all people have a monthly blood draw but might have a quarterly draw or 4 times a year. The average works well if employees are getting their blood drawn all the time but how to I get an average on people who really only do this 4 times a year? I know I need some kind of an if then or a condition but not savvy enough to figure it out. Any help is appreciated.

[NTC]

Well one approach is to do it in 2 steps. First make a simple query with your >0 condition. Call it query1. Query1 then assembles together for you the record set of all the records that deserve to be averaged.

Then use query1 as your basis to make a second aggregate query that does the averaging.


Hope it helps

[lschuh]

Now I am unable to get records to my <>0 condition. Should I put in the field and then put the criteria <>0 Or should I put in an expression with the <>0
I tried the nz() but I am only able to return an average if I manipulate the operator meaning if I have values in 4 fields then divide by 4. If I have values in all 12 months divide by 12. I want to use the 12 fields and get rid of the fields that have 0 in them so I can calulate on the values of the fields. If I have an employee with a draw in each month and divide by 12 it is great but not so for an employee who only has to have his blood drawn 4 times (quarterly). How can I work around this?