Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •