Query total has minus number

[Guest]

I'm Brand new to access. I have one field called Stroke with yes/no check
boxes. I have done a total query using the sum feature but my result is -3
when it should actually be a positive number 3. Why am I getting a
negative number for my query? Thank you so much.


SELECT Sum([Code category totals].Stroke) AS SumOfStroke
FROM [Code category totals]
HAVING (((Sum([Code category totals].Stroke))=Yes));

 

[Jeff Boyce]

I hazard a guess that your data is being kept in Access/JET. JET stores a
"True" (i.e., "Yes") as a value of -1, and a "False"/"No" as a value of 0.

If you "add"/sum a Yes/No field stored in JET, you'll get a negative number
if any are marked True/Yes.

If you want to have a positive number, try wrapping your calculation with
the Abs() function -- this returns the absolute value of (i.e., "positive")
the number.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Check boxes have a value of 0 (not checked) or -1 (checked) in Access. So
Sum-ming the negative numbers gives you a negative number.

SELECT ABS(SUM(Stroke)) as SumOfStroke
FROM [Code Category Totals]

No need for the having clause as it works after the aggregation of data
takes place.

You could have done
SELECT COUNT(Stroke) as CountOfStroke
FROM [Code Category Totals]
WHERE Stroke = Yes

 

[Guest]

Thank you SO MUCH. .... it works great.

Check boxes have a value of 0 (not checked) or -1 (checked) in Access. So
Sum-ming the negative numbers gives you a negative number.

SELECT ABS(SUM(Stroke)) as SumOfStroke
FROM [Code Category Totals]

No need for the having clause as it works after the aggregation of data
takes place.

You could have done
SELECT COUNT(Stroke) as CountOfStroke
FROM [Code Category Totals]
WHERE Stroke = Yes

I'm Brand new to access. I have one field called Stroke with yes/no check
boxes. I have done a total query using the sum feature but my result
is -3
when it should actually be a positive number 3. Why am I getting a
negative number for my query? Thank you so much.


SELECT Sum([Code category totals].Stroke) AS SumOfStroke
FROM [Code category totals]
HAVING (((Sum([Code category totals].Stroke))=Yes));