Yes/No Check Counting in Query

[Kennertoy]

Hello,



I have been struggling with a problem and still cannot find a solution. I have a form that has people's name, address, phone number, etc., AND approx 40 yes/no field options. Each person could have none, 1,2,15, or 20 checked for a yes (I assume no check is a no - db is null).

I want to report the people that have chosen more than one YES and their individual totals. When I use a query I can find all the people in the db that have chosen one activity with no problem using the criteria YES. But when I include multitude of activities and ctiteria yes in the query, it only finds people with those yes's that I checked for yes's. Someone suggested to use 'GoupBy' but I haven't had any luck. Each check has the format Acroynm then the work Check i.e. CH Check, or CMSC Check, and so on. The label associated with the check are, Text Lbl, BB Lbl, etc.

I exported the table to Excel, used and coutif function and got totals with everything from zeros to 20, then Excel would not data sort to get the data. It found the total of the yes's and of course the no's being zero. Access should do it easier.

I hope I have explained it OK. Thanks for your time.

[alansidman]

I am a little bit confused with your situation. What are your trying to total. The total yeses per person (record). The total yeses per item (field) Try to give us a real life example. It is very hard to follow your explanation. Perhaps an example of the data you have and what the expected results would look like.

[Kennertoy]

thanks Alandisman
Each record (person) has 40 or so activities (event) that we can check yes as a participant. I want to total each person's total of
YES checks. I thought I'd have to use a query to do this.

[Kennertoy]

Sorry for the misspelling - Alansidman,

Could I send you part of the table?

Kennertoy.

[alansidman]

First, a check box has a numerical value of "-1'. An unchecked box has a value of "0". If you prepare a query and in a new field type:

NewFieldName: (field1 + field2 + field3 + .........)*-1 should give you the total of all the Yeses for each record.

Look at the attached db for an example

[Kennertoy]

AlanSidman
Thanks for input.

Well I tried various combinations of your recommendation but still no luck. Can I send you my data base, to let you see my problem?

[alansidman]

Upload to this web site. Go to advanced and upload it. Be sure to run a compact and repair before uploading.

[Kennertoy]

AlanSidman,

Thank you for your help - I have finally done it!!!!!!!! I am a little thick sometimes, but I finally solved it with
your instructions. Thank you. Now if I can mark this thread complete I will get back to the data base and work on
more changes. Thanks again.