% Query

I have a table with thirty-five fields. Thirty of those fields only have two
possible options. I have another field that has a list of thirty
entries(CTM's). I would like to generate a query or report that will give me
the number of total option 2's selected for each of the thirty CTM's.

This sounds as if you may need to redesign your table structure. I am
guessing the thirty fields with only two possible options should be in a
separate table with the a field identifying the option type (which you
probably have in the field name) and the key field(s) from your current
table.

If you can't reorganize the data then you might try a union query to
normalize the data and then use the union query as the source of a totals
query. The problem may be that the Union Query would be too large.

SELECT CTMField, OptionField1 as MyValue
FROM YourTable
UNION ALL
SELECT CTMField, OptionField2
FROM YourTable
UNION ALL
....
UNION ALL
SELECT CTMField, OptionField30
FROM YourTable

SELECT CTMField, Count(MyValue)
FROM TheUnionQuery
WHERE MyValue = 2
GROUP BY CTMField

Another way to do this would be to use a complex calculated field

SELECT CTMField,
ABS(SUM(OptionField1=2)+ SUM(OptionField2=2) + Sum(OptionField3=2)+
....+Sum(OptionField30)=2)) as CountTwos
FROM YourTable
GROUP BY CTMField

"T Horner" wrote in message
...
I have a table with thirty-five fields. Thirty of those fields only have
two
possible options. I have another field that has a list of thirty
entries(CTM's). I would like to generate a query or report that will give
me
the number of total option 2's selected for each of the thirty CTM's.