Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Angry Unanswered: calculating remaining value in one field, based on second field

    Hello all,

    I am trying to calculating the remaining value in one field, based on second (Boolean) field in the same table.

    "Table1" data

    Row1 data: F1, F2, F3, 24, True (F= Field)
    Row2 data: F1, F2, F3, 10, False
    Row3 data: F1, F2, F3, 3, False

    I want to my query to display F1, F2, F3, 11

    I am using Access 2003

    I have no idea where to start.
    many thanks
    Philfr

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Basically (with F4 being the numeric column and F5 the boolean column):
    Code:
    SELECT Sum(IIf([F5]=True,[F4],[F4]*-1)) AS Total
    FROM Table1;
    But you don't explain how to group the values in F1, F2, F3.
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    2

    thanks

    Thanks Sinndho,
    works like a dream. the other fields aren't important, I can group on the one I want.
    It gives me exactly what I want.
    cheers

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

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
  •