Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mystery! Sum in Query not working (Access 2000 Win 2000 SR-1)

    I have an Access back-end supporting some webforms and on several sections of the form there are checkboxes with values of '1' if the checkbox is selected. This data gets written to a table in Access. I need to take a section of these responses and add up all the 1s submitted across n rows. In Access, I created queries off of the main table for each checkbox section and run a SUM for each column. So far so good. Now, I need to add up all the columns in the query, so I created a third query with an Expression that should give me the sum of the columns. It works for one section but not, so far, in others. Here's the SQL behind the functioning query:

    <font color=magenta>SELECT [SumBasicNeeds]![SumOfBasicNeed_Food]+[SumBasicNeeds]![SumOfBasicNeed_Housing]+[SumBasicNeeds]![SumOfBasicNeed_Short_Term_Shelter]+[SumBasicNeeds]![SumOfBasicNeed_HomeRepair_Modification]+[SumBasicNeeds]![SumOfBasicNeed_Furniture]+[SumBasicNeeds]![SumOfBasicNeed_Clothing]+[SumBasicNeeds]![SumOfBasicNeed_Utilities]+[SumBasicNeeds]![SumOfBasicNeed_Phone]+[SumBasicNeeds]![SumOfBasicNeed_Transportation]+[SumBasicNeeds]![SumOfBasicNeed_Personal_Safety]+[SumBasicNeeds]![SumOfBasicNeed_Money_Management]+[SumBasicNeeds]![SumOfBasicNeed_Financial_Assistance] AS BasicNeedsTotal
    FROM SumBasicNeeds;</font color=magenta>

    And I get 17 as the result.

    HOWEVER, when I do the same thing to another group, nothing shows when I run the query. This is the non-functioning SQL: <font color=magenta>SELECT [SumFamily]![SumOfFamily_Support_Need_Respite]+[SumFamily]![SumOfFamily_Support_Need_Child_Care]+[SumFamily]![SumOfFamily_Support_Need_Adult_Child_Care] AS TotalFamily
    FROM SumFamily;</font color=magenta>.

    The source table has the Basic Needs info set up as Number/Byte and the Family Needs was Number/Long Integer; I changed it to Number/Byte but that didn't seem to make any difference.

    ??? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Mystery! Sum in Query not working (Access 2000 Win 2000 SR-1)

    It sounds as if you are getting a null value as one of the pieces of data returned by the second query and therefore the sum of the fields gives you nothing. Try NZ(SumOf..., 0) + NZ (SumOF...,0) to see if you can replace the null value with a zero.

    Or check your second query to ensure each field is returning data.

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mystery! Sum in Query not working (Access 2000 Win 2000 SR-1)

    thanks for the reply. yes, that seems to be the problem. i went to the source table and added zeros and now the second sum query works. huh! pesky nulls.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Mystery! Sum in Query not working (Access 2000 Win 2000 SR-1)

    If you solved this by going into the table and changing null to zero then won't you get the same problem as you have just encountered?

    What Gary suggested was to change your query to take care of the nulls.

    If I am on the wrong tram please ignore this post.

    Pat

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mystery! Sum in Query not working (Access 2000 Win 2000 SR-1)

    the table was also changed to make the data number type with default to 0 so all new data will not be null. <>"" I suppose.

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

    Re: Mystery! Sum in Query not working (Access 2000 Win 2000 SR-1)

    Don't EVER count on that. If the field is numeric, it can still be set to Null. The default only means a zero will be entered into a new record, not that it will always be there. Furthermore, if you import data into a table, nulls will overwrite any existing default values.
    Charlotte

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mystery! Sum in Query not working (Access 2000 Win 2000 SR-1)

    I'm not terribly worried about getting Nulls back in as the db will only be accessed from a web form -- and every time a new record is created (via INSERT) 0's will be put in all the appropriate fields. obviously, *I* could muck things up during development but once it's in production db interaction will be limited to report generation and archiving out old data. Unless the form itself changes and data elements get changed or removed.

    I do find it a little odd that SUM doesn't function if there are null values in fields. How can Null ever be counted in a sum? Also, the problem seems more persistent when field type is set to Numeric. I have some other fields that may or may not have null values but SUM seems to work with a number in a column and the field type set to text. It's a little hard to test because you can't tell by looking if a cell is Null or simply blank.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mystery! Sum in Query not working (Access 2000 Win 2000 SR-1)

    The Sum function will ignore Null values, which is equivalent to treating them as zeros, but if all values being summed are Null, Sum will return Null, not 0.

    Null values are not ignored in expressions using +. If at least one of the arguments is Null, the result will be Null. You can verify this in the Immediate window of the Visual Basic Editor by typing ? 3+Null followed by Enter.

    <table border=1><td align=right>Expression</td><td align=right>Result</td><td align=right>3+0</td><td align=right>3</td><td align=right>3+Null</td><td align=right>Null</td><td align=right>Null+0</td><td align=right>Null</td><td align=right>Null+Null</td><td align=right>Null</td></table>
    So if you have a Totals query that sums fields, the sum will be Null if all values in that field are Null. Subsequently, if you add the sums in an expression with + signs, the result will be Null if at least one of the individual sums is Null.

Posting Permissions

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