Query total has minus number

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ferde - 27 Jul 2006 18:21 GMT

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));

Reply to this Message

Jeff Boyce - 27 Jul 2006 18:29 GMT

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

> 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
[quoted text clipped - 5 lines]
> FROM [Code category totals]
> HAVING (((Sum([Code category totals].Stroke))=Yes));

Reply to this Message

John Spencer - 27 Jul 2006 18:34 GMT

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

Reply to this Message

ferde - 27 Jul 2006 19:29 GMT

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.
[quoted text clipped - 19 lines]
> > FROM [Code category totals]
> > HAVING (((Sum([Code category totals].Stroke))=Yes));

Reply to this Message