sum of a unique value in a group

[lstutchman]

i'm trying to get a sum of yes, no and NA in a query over four different
fields. my table fields go like this [message] [callclose] [directory]
[signout]. each field has a value of yes, no or na. i'm trying to get a
count of how many yes, no and na in each field. is it possiable to do this in
a query?

 

[Marshall Barton]

i'm trying to get a sum of yes, no and NA in a query over four different
fields. my table fields go like this [message] [callclose] [directory]
[signout]. each field has a value of yes, no or na. i'm trying to get a
count of how many yes, no and na in each field. is it possiable to do this in
a query?

Kind of ugly, but it will do it:

SELECT Sum(IIf([message] = "Yes", 1, 0)) As MsgYes,
Sum(IIf([message] = "No", 1, 0)) As MsgNo,
Sum(IIf([message] = "NA", 1, 0)) As MsgNA,
Sum(IIf([callclose] = "Yes", 1, 0)) As CallYes,
. . .
FROM thetable

There's probably a way to use a cross tab query that
wouldn't be so tedious, but I'm not sure.