how to sum multi fields with one criteria

[alhareri]

hi every one
how to sum multi fields with one criteria for example:


i have form
with 5 fields all the fields has the same choice A,B,C,D
i want to count how many A in all 5 fields
or how many B in all 5 Fields and so on
thanks

[ranman256]

make 1 query to sum the fields, but the last field sums ALL":
select sum(a), sum(b), sum(c), sum(d), (sum(a)+ sum(b)+ sum(c)+ sum(d)) from table

or do it in 2 queries:
Q1 to sum the fields
Q2 to Total all together from Q1

[rpeare]

If you are using this as a scoring mechanism I would attach the A B C D E values to what their score means rather than trying to count how many of an item there are. Otherwise you'll be stuck doing something like

ACount: iif([1]='a',1)+iif([2]='a',1)+iif([3]='a',1)+iif([4]='a',1)+iif([5]='a',1)

and doing something similar for each discrete value in your list (you have 10 values or so) so it would be quite ponderous. The other option is to turn this data into a normalized structure.

[alhareri]

thanks for you it is work fine