Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Georgetown, Grand Cayman, Cayman Islands
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Averaging Values (2000)

    I have a form created that is for entering survey information. typical contact info fields at the top, and then 18 questions, with an option group to select one answer. Excellent, Good, Average, Poor, Terrible and Not Evaluated.

    The option value for excellent is 5, good is 4, etc etc and Not Evaluated is 0(zero)

    My problem is that the Not Evaluated fields are interfering with the average.. if someone puts down Not Evaluated, Access is averaging in a zero, which makes my averages go down, and makes me look bad! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    What I want to do is when someone puts in a Not Evaluated, then to remove that from the total number of records counted for the average.

    like... if option_value=0 then count=count-1 or something like that, but I dont know how to do that in Access

    Thanks
    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Averaging Values (2000)

    How are you calculating the average? Post the formula or method.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Georgetown, Grand Cayman, Cayman Islands
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Values (2000)

    I created a select query to select the values that I wanted in the report, and then used the summary options in the report wizard. After it was done, I went and looked at it and it says:
    =avg([fieldname]) in the control source, where fieldname was one of the questions on the survey

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Values (2000)

    Looks to me like you want to leave your field null for "not evaluated"; then avg will do what you want it to do - see the help for avg.

    This is one of the cases where null values are useful (I wish there was a way of telling Access not to assume I want 0 as a default for numeric values!).

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Georgetown, Grand Cayman, Cayman Islands
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Values (2000)

    YES. I think that's exactly what I want to do! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    However, I cant figure out how to set the Option button to resolve to null as it's "option value" if I leave it blank, Clippy knocks on my screen and says the setting isnt valid, and same with "" for an empty string, as it doesnt work with number fields..

    im so close now I can taste it! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Values (2000)

    I don't use a separate option button in my option groups for "Missing" (or "Not Evaluated" in your case - Null normally means "Missing" in my databases). Assuming you haven't set a default for the option group and the field isn't required, you'll get a null back from an option group if nothing is clicked.

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Georgetown, Grand Cayman, Cayman Islands
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Values (2000)

    OK thanks.. I was trying to avoid having to re-do it all like that again, but that still leaves me without the "not evaluated" field checked.. the boss likes to see how many people ticked off not evaluated (i.e. they didnt attend that function, so they could evaluate it)
    is there a way for me to do it so they still tick off "not evaluated" and then just not have it include that one?

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Averaging Values (2000)

    If he wants to see it, then it's a legitimate value in the averaging. He'll have to take his pick, an unskewed average or a value that doesn't really mean anything. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Values (2000)

    Why not change your Query to not select records where the value is zero?
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging Values (2000)

    One more thought ...Can you use the DAvg function and set the criteria to: [fieldname]<>0?

Posting Permissions

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