Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excluding negative lines in a sum (2000)

    I want to be able to sum only numbers in a field that are >0. For example if a table contains 3 records with values {5,10,-20} in the field I want to sum, I want to sum only the 5 and 10 for a total of 15. I want to ignore totally the value -20 in the calculation. How can this be done. Thanks.

    Wayne

  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: Excluding negative lines in a sum (2000)

    Assuming your table is named Table1 with the numbers stored in a field called number.

    - Create a new query and select Table1 as the data source.

    - In the Query Grid
    --Select Number, go to View totals and set the group to sum. (This adds all of your numbers together)

    NOW
    --Select Number again
    --Set the total to Expression with a criteria > 0
    -- Uncheck the show checkbox

    Run your query for the result. See the attachment to see this graphically.

    HTH
    Attached Files Attached Files
    Regards,

    Gary
    (It's been a while!)

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

    Re: Excluding negative lines in a sum (2000)

    You already asked this in <post#=218488>post 218488</post#> and got some replies there. It would have been better to continue the discussion in that thread; if the replies weren't clear enough, you could have posted a reply to those, with the additional information you supplied here. This is not meant to scare you off; you're very welcome to post questions in the future. But please keep in mind that it's more convenient to others browsing this forum if a single subject is handled in one 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
  •