ABS FUNCTION NOT WORKING PROPERLY 1

[faxpay]

I have an Access 2000 db and am writing a SQL statement using the ABS function. Adding a column of numbers in which there is one negative number "-230". I am using the ABS function to have the negative added as a positive.

What is happening is that the 230 is being added twice so that the result is 230 larger than it should be.

SUM(ABS(WAMT)) AS TOTALEARN

wHY IS IT ADDING THE 230 TWICE?

Tom
faxpay

 

[PHV]

there is one negative number "-230".
So, expexted result: SUM(ABS(WAMT)) = SUM(WAMT) + (2 * 230)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[faxpay]

I'm sorry PH. I don't get it. Why does it go 2*230 ??
How do I get it to add the negative numbers just once??

Tom
faxpay

 

[PHV]

[/i]Why does it go 2*230 ??[/i]
Simple example with 2 values:
WAMT: 100, -230
SUM(WAMT): [!]-130[/!]
SUM(ABS(WAMT)): 330
Diff: 460 (as expected).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

Can you explain to us how you are expecting the ABS function to work? As PHV showed, the answer you got is the one we would expect to get.....

Leslie

 

[faxpay]

Hi Leslie,

What I am expecting is the -230 to be treated as a positive number and simply be added to the summation of the column as +230.

Does not absolute value mean the 230 being treated as a positive number. In other words the absolute value of -230 is |230| NO???? Where am I missing it.

Thank You for your patience.
Tom
faxpay

 

[PHV]

When you do a normal sum, 230 is SUBTRACTED.
When you do a sum of ABS, 230 is ADDED.
Thus the difference of 2 * 230 between the two results:
One time for 230 NOT being subtracted and one time for being added.
Still not clear ?
What is your meaning about my post stamped 4 Apr 07 15:51 ?


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[faxpay]

PH

OK, I finally got it. I had to actually add up the columns to see what is happening.

What I am actually trying to do is eliminate any negative values from being included in the second total that I take. I see now the ABS function is not what I want. What would I use to keep the negative values from being included in the total and still bring both totals up on the one select statement??

My partial stmnt:

SELECT EMPNO, SUM(WAMT)AS TAXABLEEARN, SUM(???(WAMT))AS TOTALEARN FROM

The second sum not including the negative numbers.


Tom
faxpay

 

[PHV]

SELECT EMPNO, SUM(WAMT) AS TAXABLEEARN, SUM(IIf(WAMT>0,WAMT,0)) AS TOTALEARN FROM

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[faxpay]

PH

That IIF function does it. I thank you for all your help and patience. And thank You Leslie.

Tom
faxpay

 

[lespaul]

no problem, just trying to help clarify the real issue.

Have a great day!

Leslie