Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Totals Query giving minus results

    I have a Query that gives me the totals of certain fields within my table for a quick and easy method.

    Code:
    SELECT TBL_Wills.Active, Sum(TBL_Wills.ExecutorOther) AS [Total of ExecutorOther], Sum(TBL_Wills.ExecutorPalmers) AS [Total of ExecutorPalmers], Sum(TBL_Wills.ExecutorBoth) AS [Total of ExecutorBoth]
    FROM TBL_Wills
    WHERE (((TBL_Wills.Active)=True))
    GROUP BY TBL_Wills.Active;
    By the results are correct but in the minuses? Any idea why this is doing that?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    the only way that's going to happen is if the underlaying data is negative.

    is it?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    no. its summing the answers correctly but has '-' in front of it, so i sound assume this is a negative sum result? but it shouldn't be?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Are you positive? Did you look at the underlying table to see if it has any negative values in it?

    Try this:

    Code:
    SELECT * 
    FROM TBL_Wills
    WHERE ExecutorOther < 0 OR ExecutorPalmers < 0 OR ExecutorBoth < 0
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    None of them are booleans yah?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2004
    Posts
    287
    it shows all records appropriately using that code.

  7. #7
    Join Date
    Mar 2004
    Posts
    287
    "None of them are booleans yah?"
    What do you mean by that?

  8. #8
    Join Date
    Mar 2004
    Posts
    287
    ExecutorOther, ExecutorPalmers & ExecutorBoth are tick boxes by the way?!

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    true is represented as -1.

    If none of your records were negative, you would have returned ZERO records using the last query I provided.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Mar 2004
    Posts
    287
    ok i understand, so how can i change the totals to read positively as a total?
    so it counts up the records and not the tickbox fields?

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes\ No is just another term for boolean in access. Check out the ABS function in help - you can wrap your values in that.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Mar 2004
    Posts
    287
    thanks but what do i look up in help as searching for ABS doesn't work?

    I know it may be a silly question but hey!! I'm silly LOL

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what did you try
    how did you try to use the ABS fucntion and where

    what does your data look like
    can you easily explain in english what the data is, how it is stored and what you wan tot do with the sum / count (incidentally is if count or sum you want)?

    BTW personally ID drop the spaces between "[Total of ExecutorOther]" to become TotalofExecutorOther. reason it will make you life easier writing reports and queries and forms in the long term.. you wont have to encase them in []
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by NeilMansell
    thanks but what do i look up in help as searching for ABS doesn't work?
    When I search for ABS the first few results I get are list of functions (by name, by type etc.

    Functions sorted alphabetically > go to 'A' > ABS is the first one.
    Come on Neil, open those eyes!
    George
    Home | Blog

Posting Permissions

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