count of checkboxes

[rooterr]

I have a log table which has a date complete and a checkbox for all of the tasks that were done for each job.

Job Road Stream line complete
1 yes no yes 4/1/02
2 Yes yes no 5/1/02
3 Yes no yes 5/2/02

I want to count all of the tasks that were completed each month by task. so the result I am looking for would be:

Month Road Stream line
April 1 0 1
May 3 1 1

I know this is simple just never done it and don't have time to play to figure it out. Any help would be appreciated

 

[WildHare]

Here are some thoughts that might point you in the right direction.

If a checkbox is 'true', it has a non-zero value. ( -1 by default )

Generally, you can evaluate checkboxes and other binary objects by remembering that

True = -1 = YES and False = 0 = NO

An IF statement implies truth:

IF Checkbox then is same as
IF Checkbox = True then

(Note: the former style of syntax is frowned upon by some purists because it causes Access to reevaluate CHECKBOX each time its called, thus wasting over 15 cpu cycles. Decide for yourself...)


A COUNT of Checkboxes will only count those that are NON-zero.


Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[jhall156]

You could try
SELECT DatePart('m',complete) as CompMonth,
SUM(ABS(Road)) as Road, SUM(ABS(Stream))As Stream, SUM(ABS(Line)) AS Line
FROM yourlogtable
GROUP BY DatePart('m',complete)

It should work because the Trues are stored as -1 and the falses are stored as 0.

Worth a shot
JHall

 

[rooterr]

Thanks a lot guys the information was very helpful in understanding what was happening. The select statement worked like a charm JHall.

Thanks,

rooterr