count three values in multiple fields

[Dale]

I have a table that stores a number (primary key), a name , and 26
fields that are answered with only "Y"."N", or "NA". I am trying to
count the number of y's, n's and na's for each of the 26 fields. I
have tried crosstabs but I get numbers that are either the total of
all recores or some combination of records.
## name A B C D E F G
ie. 111113 Dan y y n y n na y
111112 Dan y y y y na y n

should count : A B C D E F G

Y 2 2 1 2 0 1 1
N 0 0 1 0 1 0 1
NA 0 0 0 0 1 1 0


I hope I have explained this well enough. I can extract the
information I need if I create a query that only uses one of the
y,n,na at a time but I'm sure there has got to be a simpler way. Any
help would be greatly appreciated.
Thanks,
Dale

 

[Golfinray]

In the query criteria field you could us Like "Y" or like "N" or Like "NA"

 

[John Spencer]

There really isn't a much simpler way since your table structure is wrong. It
should be more like

RespondentNumber
Name and other info about the respondent

QuestionResponses
RespondentNumber
QuestionID (A,B,C,D, etc.)
Response (Y,N, N/A)

With that minimal structure your analysis is trivial.

If you are stuck with the current structure

SELECT Abs(Sum(A="Y")) As AY
, Abs(Sum(A="N")) as AN
, Abs(Sum(A="NA")) as ANA
, Abs(Sum(B="Y")) As BY
, Abs(Sum(B="N")) as BN
, Abs(Sum(B="NA")) as BNA
....
FROM YourTable

With the proper structure, that query would be
SELECT QuestionID, Response, Count(Response) as CountEm
FROM YourTable
GROUP BY QuestionID, Response

You could try writing a UNION query to get your data into the proper form, but
with 26 fields this may fail

SELECT SurveyID, "A" as Question, A as Answer
FROM YourTable

UNION ALL
SELECT SurveyID, "B" as Question, B as Answer
FROM YourTable
....
UNION ALL
SELECT SurveyID, "Z" as Question, Z as Answer
FROM YourTable

THen use that as the source query to get your counts.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County