calculating remaining value in one field, based on second field

philfr · #1 (**permalink**) 10-08-11, 03:32

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

Sinndho · #2 (**permalink**) 10-08-11, 04:07

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.

philfr · #3 (**permalink**) 10-09-11, 06:20

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

Sinndho · #4 (**permalink**) 10-09-11, 15:15

You're welcome!

Internet Services

Web Development

Digital Marketing

Consumer Tech