Counting Ticked Check Boxes in a report...

[JoysTick]

I've got a table [tblClients] and another table [tblClientVisits] linked to the client table by a foreign key [ClientNumber]

In the [tblClientVisits] table I've got a large number of Yes/No Question fields. I'm selecting groups of Clients in a query by [Gender] and [VisitDate] and so a Client may have none, one or many visits in the period.

I'm trying to find out how many Unique Male clients and Female clients answered Yes to each question. Whether or not they answered yes once or five times on single or multiple visits is not important. As there are quite a few questions I'm not wanting to set up a query for each of them. I've got a report based on a query that counts how many times each Yes/No was ticked, but was thinking that it may be better to do the count of unique Clients for each of these within a report.

In the report, I think I'd try to get something like;
DCount("[MyTable]![MyYesNoField]",[MyTable]","Something here to count unique client numbers that answered yes to the question")

but I am not sure of the code to do the last bit!

How can I achieve this aim?
tx

 

[boblarson]

There may be a better way to do this, however this is the way I currently accomplish the same thing.

First, I create a query to do the Unique Count.

In your case I would select the fields necessary from the two tables. These fields would be: [Gender], [ClientNumber], [VisitDate], [YesNo], and repeat the [YesNo] again but like this: cntClient:YesNo

Then click on the GROUP button (looks like the SUM character). A line that says GROUP BY should appear for each field. Make sure that the fields [Gender], [ClientNumber], [VisitDate], [YesNo] (criteria for YesNo set to Yes) all have GROUP BY in that line. Then change the field you repeated at the end-cntYesNo:YesNO from GROUP BY to COUNT.

Save the query.

The query should give you a count of the Yes responses of a particular date that the clients made. You can choose to not include [Gender] if you don't want to break that down in your report. Otherwise the query breaks it down by gender and then by client number and then the total of visits for a certain date.

You can then use that query in a report or sub-report.

Hope that helps.

Bob Larson