# Thread: Query Problem (Access 2K)

1. ## Query Problem (Access 2K)

I am having a slight problem with the correct way to write the following expression in a query. I have a form with several option groups that result in the field either being null or being populated with a number from 1-7. The query I am running uses an IIF function

QuestionPos1: IIf(IsNull([Question1]),Null,IIf([Question1]<6,0,1))

to either put a 1 if the result was a 6 or 7 or a 0 if the number was <6. The field remains as NULL if it was originally NULL.

However, I would like to add an additional column that takes the average of the 1's and 0's so that I can get a percentage of the 6 fields (Actually survey questions) so I can get an overall % of the positive results for each record (positive result is 6 or 7 on a question) The problem I run into likely results from the Null fields, although I have not been able to find a workaround. Another way to look at this is to count the number of 1's and divide by the number of 1's and 0's, but I am having trouble making this work too. I get an error trying to use AVG function in the query even when there are no NULLs in the record.

Thanks in advance for any assistance.

2. ## Re: Query Problem (Access 2K)

The Avg function calculates the average of the values in a single field over all records. My impression is that you want to calculate the average of a number of fields within a single record. Is that correct?

3. ## Re: Query Problem (Access 2K)

Yes, Hans that is correct. The problem also exists that some of the fields in the record will have a NULL value.

Thanks

4. ## Re: Query Problem (Access 2K)

Hi Marc,

You have been active here for a while, so it probably won't come as a suprise if I tell you that I think the data design is wrong. Instead of having 6 fields Question1 ... Question6 in a record, you should have a separate table with the ID from the main table, and for each ID 6 records, each with the score for one question. That would make it very easy to calculate the average per ID in a totals query grouped by ID.

In the present design, you will need a custom field aggregate function. Put the following code in a standard module:

Public Function FAvg(ParamArray s() As Variant) As Variant
' Returns the average of valid numbers in the list supplied
Dim dblSum As Double
Dim i As Integer
Dim varValue As Variant
Dim intCount As Integer
For i = LBound(s) To UBound(s)
varValue = s(i)
If IsNumeric(varValue) Then
dblSum = dblSum + Val(varValue)
intCount = intCount + 1
End If
Next i
If intCount = 0 Then
FAvg = Null
Else
FAvg = dblSum / intCount
End If
End Function

This function will ignore all non-numeric values, including nulls. Use it like this in a query based on the query in which you compute QuestionPos1 etc.:

AverageResult: FAvg([QuestionPos1], [QuestionPos2], [QuestionPos3], [QuestionPos4], [QuestionPos5], [QuestionPos6])

5. ## Re: Query Problem (Access 2K)

I apreciate the assistance and don't have a problem with any criticisms. I am still a little confused by your description of the proper data design, but will definitely experiment with this. I guess I am so used to thinking one survey=one record with a separate field for each question.

Again, as always you are an asset and always appreciated.

Thanks Hans.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•