sum of a unique value in a group

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
lstutchman said:
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?
Click to expand...


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.
 
Back
Top