COUNTING Yes Values in Checkbox in a Query

[manix]

Hi All, I have read a few posts on here but can't quite get a solution to my particular issue.

I have two tables in a query:

tbl_suppliers
tbl_supplier_perf

tbl_suppliers is right joined with tbl_supplier_perf by

[Location Name]----->[Supplier]

No as part of tbl_supplier_perf there is a YES/NO checkbox, where it can be ticked if there is an issue with a supplier delivery. This field is called [Issue?].

I want to report all suppliers (not just those with records in tbl_supplier_perf) with a count of the amount of records created in tbl_supplier_perf with a tick in [Issue?]. So if no records in tbl_supplier_perf have [Issue?] ticked it will just report 0.

Basically the query needs to report all suppliers with a count of how many records have been ticked "YES". It is a check box so I believe they are recorded as 0 and -1.

I believe I need to use Dcount but I do not know how to get that in to my existing query!

 

[Banana]

DCount is for VBA procedures.

For queries, you just need to right-click the grid where you put in field and select "Totals" to reveal the hidden row that lets you group by and do aggegrate functions, including count.

 

[manix]

DCount is for VBA procedures.

For queries, you just need to right-click the grid where you put in field and select "Totals" to reveal the hidden row that lets you group by and do aggegrate functions, including count.

This is true, but the simple count function just counts all of the records, it is not differentiating between those ticked and those not ticked.

 

[rainman89]

put the criteria of yes in the field you want counted

 

[manix]

put the criteria of yes in the field you want counted

Thanks Ray, but doing this does not work either. It is not differentiating between ticked and unticked, it just shows the total of records with check boxes, not whether they are ticked !

 

[rainman89]

ok try true then
if the criteria of the field that has the checkbox in it is set to true, then it should only count the true answers...

 

[Banana]

Rainman's right.

SQL should be something like this:

SELECT Count([Issue]) from tblSupplier WHERE Issue=-1;

It'll be a bit more complicated as you mentioned joining two tables, but if you're getting stuck, start with just one table and one field as in the sample SQL above, then see what happens. You may be seeing a problem due to extranenous grouping by or something like that may return erroneous results.

BTW, -1=True=Yes. All are same.

 

[Brianwarnock]

If you cannot just select the records with the checked checkbox then you will need to do a sum if

Countchecked:Sum(IIF(checkboxfldname=TRUE,1,0))

Brian

 

[MStCyr]

Here's a tip that came in from Tech Republic


http://blogs.techrepublic.com.com/msoffice/?p=352&tag=nl.e056

 

[Brianwarnock]

I feel a right prat its so damned obvious isn't it
Sum(Abs(checkbox))

Thanks MStCyr

Brian