Average of an option group

[StephenB]

I have a report that's based on a query whose form has an option group. The Options (and Values) are N/A (0), Yes (1) and No (2). I have the option group set to store the numeric value in the table. I need to report the average of the option group without including the N/A's.

I need three records with the results of "Yes", "No" and "N/A" to return an average of "1.5", not "1". Any suggestions? It's still early in the developement of the db, so I'm open to making changes on any object.

 

[Pat Hartman]

The easiest way to do this is to replace the 0 values with nulls since the aggregate functions ignore nulls.

Select ...., IIf(OptionFld = 0, null, OptionFld) As OptionField, ...

 

[StephenB]

Thank you, Pat. I had been working the issue the way you are suggesting, but everything I had tried failed. I'm not familiar with writing the code you mention above. Could you provide more information on the code itself and where/how I should use it? I'd be grateful.

Thanks in advance,
Stephen

 

[Pat Hartman]

You would put the IIf() function in the query that the report is based on. Just replace the Option field with the IIf().

Select fld1, fld2, fld3, IIf(OptionFld = 0, null, OptionFld) As OptionField, fld5, fld6
From YourTable;

I used OptionFld as the field name. Replace that with your actual field name.