Counting "check marks"......

[chef_tim]

Good day, I've been through pages and pages of searchs on count, sum etc. All I want to do is count the number of "check marks" ie yes on a report. I've tried =count([10kforklift]) I've also been in the help file with no luck at all. Thanks for any help, Tim

 

[Rickster57]

check

Try using the dcount function. This allows for a criteria which in this case would be "Yes".
There should be plenty of examples in this forum for dcount

 

[mhartman]

Hello:

SELECT Count(tblEmployees.TractorID) AS CountOfTractorID, tblEmployees.PowerSteering
FROM tblEmployees
GROUP BY tblEmployees.PowerSteering
HAVING (((tblEmployees.PowerSteering)=True));

Regards
Mark

 

[mhartman]

Hello again

I forgot to add my explanation:

This will count all the records in a table named Employees that have PowerSteering checked in the "PowerSteering" field. Add the TractorID and PowerSteering field to the query only!

If you need the file, Let me know.

Regards
Mark

 

[mhartman]

Hello: Just in case I meet an untimely demise, heres the file.

Regards
Mark

 

[Rich]

=Sum(Iif([MyCheckbox],1,0))

 

[chef_tim]

Thanks for all the replies. Mark, I'm sorry I havn't a clue what you're talking about (just way to deep for me), and you're example was password protected. Rich, I used yours but now if you would fill me in on what the ,1,0 do/mean???? I get the rest. Thanks again, Tim

 

[Rich]

It's simply summing the true values, if the checkbox is true then it's one, false it's zero

 

[RoyVidar]

Or, one could use
=Sum(ABS([10kforklift]))

 

[Moniker]

Roy's is the simplest answer there. Checkboxes are stored with a 0 for FALSE and a -1 for TRUE. Doing what he suggested:

=Sum(ABS([10kforklift]))

will count all the records. However, since zeroes are FALSE records, they don't affect the sum total. The ABS is for absolute number and it just converts a negative number into a positive one. Using a SUM function is also faster than using IF statements.

~Moniker

 

[RoyVidar]

Can be taken a step further, too, say you want to know in how many records the text "test" is found in the field Blah, then

=Sum(ABS([blah] like "*test*"))

 

[Rich]

Roy's is the simplest answer there. Checkboxes are stored with a 0 for FALSE and a -1 for TRUE. Doing what he suggested:

=Sum(ABS([10kforklift]))

will count all the records. However, since zeroes are FALSE records, they don't affect the sum total. The ABS is for absolute number and it just converts a negative number into a positive one. Using a SUM function is also faster than using IF statements.

~Moniker

Roys answer has two functions can you prove it's faster than the Sum(Iif ?

 

[Moniker]

SUM is an aggregate function and ABS is a conversion function.

IIF is a conditional function, where at least two conditions have to be supplied and both must be evaluated and valid, even if only one condition is used.

By rule, conditional statements are slower than aggregates and conversions.

In looking at Roy's example again, it can be made just a few milliseconds faster by putting the conversion outside the aggregate, like so:

=ABS(SUM([10kforklift))

This way, the SUM aggregate function runs once, and the ABS runs once. Previously, the ABS was running for each value.

Keep in mind that unless you are working with millions of records (which I do), these little "speed up" tricks don't really have a noticeable effect. It's just good coding etiquette. However, I'm just anal about it that way. If you remember watching directory scrolls on a 386/12 to see how fast the processor was, then this makes sense to you.

~Moniker

 

[chef_tim]

I used Riches and it worked, but I'm also going to experiment with the SUM/ABS solution just to get a feel for it. I certainly appriciate the education I'm getting here. Tim