Summing question, many fields, 1 record 2

[mrtauntaun]

I have a table with 40 or so yes/no fields (check boxes); a questionarre. I would like a query to return the total number of yes's it finds per record. I have it set up so only one record is queried at a time (as the query will be run from a form, and only the current record will be queried, based on the primary key).
Now, if this were multiple records, I know how to sum them up to get a total, but these are multiple fields for one record.
How can I do this? Is it easier to write a vba script to add it up right on the form? I'd rather do it by query, but all input is welcome.
Not being too good with queries, I thought I'd ask. Thanx in advance!

 

[lespaul]

Something like this might work:

SELECT PersonIDField, COUNT(A.Answer) As TotalYes FROM
(SELECT PersonIDField As PersonIDField, Answer1Field As Answer From TableName WHERE Answer1Field = True
UNION
SELECT PersonIDField, Answer2Field From TableName WHERE Answer2Field = True
etc...
UNION
SELECT PersonIDField, Answer40Field From TableName WHERE Answer40Field = True)
GROUP BY PersonIDField

Another option depeding on how far along in development you are is to rethink your table design and normalize. Read The Fundamentals of Relational Database Design. That's what the big union query is doing is normalizing the structure.

HTH

Leslie

 

[PHV]

In the query grid:
TotalOfYes: Abs([yesno field1]+ ... + [yesno field40])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mrtauntaun]

Thanx to both!
Both solutions accomplish the task, though I think I am going to go with the second as it is slightly less typing. Thanx again!

 

[lespaul]

you may also want to consider typing the UNION query once and saving it: qryNormalized

this way you can use this query any time you need to do a calculation on this information.

Leslie