Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2015
    Posts
    3

    Unhappy Unanswered: Please Help! Zeros Are A Nightmare!!!!!

    Hi All,

    I'm having an issue with Zeros in my Access 2013 Database.
    I have a Query that takes two values and Averages them.
    However it does not take Zeros in to account, for example:

    1 + 3 / 2 = 2 {The database can figure this out, great.}
    0 + 2 / 2 = 2 {The database can not use zeros, at all!}

    The SQL Statement running the query is here:

    SELECT [Max Of Score].[Internal Student #], Avg([Max Of Score].MaxOfScore) AS AvgOfMaxOfScore
    FROM [Max Of Score]
    GROUP BY [Max Of Score].[Internal Student #];

    I have been trying to fix this for days now. I've tried countless Google searches and forum trawling,
    but no-one seems to have an appropriate fix. People only seem to care about ignoring Zeros (Which it does a pretty good Job of already!)

    Any help will be greatly appreciated!

    Many Thanks,
    Richard Howe

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the problem is that you have a misconception between what you think 0 represents and what any computer language thinks 0 represents. in this case consider using NULL (effectively no result) in place of zero.
    if you are doing an average and supply a zero in the datastream then the computer treats that as a value. if you supply a null then that value isn't included in the calculation.

    a zero in this context means a student scored 0 in that assesment. a NULL means the student didn't take that assessment or the result isn't available as yet. But be aware that NULL's come with their own issues, you may need to use the NZ function to givce a value where NMULL sint' appropriate, and you cannot test if somethign equals NULL, use the isnull function in place.

    Code:
    case  mark1 mark2 mark3 mark4 mark5 avmark
      1     1     2     3     4     5     3
      2     0     2     0     4     5     2.2
      3    NULL   2    NULL   4     5     3.66667
    if it helps you are not the first, and Im sure you wont be the last to fall foul of this. years ago I remember being pestered by a maths lecturer who claimed to have found a bug in VBA average functions. After calming down her excitable Italian approach it was pointed out that null is not 0 and vice versa. changign the dataset to reflect that cleared the fault and she (begrudingly accepted that perhaps VBA was right )
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2015
    Posts
    3

    Unhappy

    Hi healdem,

    Thanks for your response!
    I am looking to get the result from your demonstration "Case 2"
    However, Access gives me the result of Case 3 regardless of the presence of "0" or "NULL"

    Suggestions?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so can we see your data, to avoid having to create a table + test data.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2015
    Posts
    3
    I'm an idiot!

    Sorry, after some further investigation and tracing the data back to its source I found a criteria set on one of my other queries that stated >0.001
    Removing this will cause some small issues with other grade point calculations, but will ultimately solve my "0" issue.
    Thank you very much for your help, please accept my apologies for my stupidity!!

Tags for this Thread

Posting Permissions

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