Count field values

[wal50]

I have several tables that contain an account number and the answers to
between two and ten questions. The possible answers are yes, no or N/A, no
null values.
For each record, I need to get a count for each answer. (# of yes, # no and
# n/a)
My query using dcount gives me the record count in the table.
Help is appreciated.
Thanks
wal50

 

[Baz]

SELECT some_answer, Count(some_answer) FROM some_table GROUP BY some_answer

 

[John Spencer]

I'm not sure whether you want to get the number of yes answers over the
table or the number of yes answers per record. Also, I infer that your
table structure is something like: AccountNumber, Question1, Question2,
Question3, Question4, ....Question10. That structure can make it tough.

To count all the Yes answers in question 1 and all the no answers in
question1 you would need something like
SELECT ABS(SUM([QuestionResponse1] ="yes")) as YesAnswers
, ABS(SUM([QuestionResponse1] ="No")) as NoAnswers
FROM YourTable

It would probably be best to build a union query to fix (normalize) your
data and then use that query as the source for analyzing responses.

SELECT AccountNumber, "Question1" as QuestionNumber, Question1 as Response
FROM YourTable
UNION ALL
SELECT AccountNumber, "Question2" as QuestionNumber, Question2
FROM YourTable
UNION ALL
....
SELECT AccountNumber, "Question10" as QuestionNumber, Question10
FROM YourTable

Save that as YourTableNormed

Then it is easy to get answers to questions like

how many people answered Yes, no, N/a to each question
SELECT QuestionNumber, Response, Count(Response) as FreqCount
FROM YourTableNormed
GROUP BY QuestionNumber, Response

How many yes answers were made by each accountnumber
SELECT AccountNumber, Count(Response) as FreqCount
FROM YourTableNormed
WHERE Response = "Yes"
GROUP BY AccountNumber

How many responses of each type were made by each account
SELECT AccountNumber, Response, Count(Response) as FreqCount
FROM YourTableNormed
GROUP BY AccountNumber, Response

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Gary Walter]

I have several tables that contain an account number and the answers to
between two and ten questions. The possible answers are yes, no or N/A,
no
null values.
For each record, I need to get a count for each answer. (# of yes, # no
and
# n/a)
My query using dcount gives me the record count in the table.
Help is appreciated.

Meaning no offense, but it sounds like
you designed your tables like they were
Excel spreadsheets.....

If so, the "best" solution is to export them
to Excel and perform the counts there.

Please accept my apologies if I misunderstood,
but it sounds like you have table structure like:

AcctNum Ans1 Ans2 Ans3 Ans4 Ans5
1 yes no no N/A yes
2 no N/A no no no

if so, then it will serve you better to normalize
your data (possibly with UNION query like

qryUnion

SELECT
AcctNum,
"Ans1" As Ans,
Ans1 As AnsVal
FROM
yurtable
UNION
SELECT
AcctNum,
"Ans2",
Ans2
FROM
yurtable
UNION
SELECT
AcctNum,
"Ans3",
Ans3
FROM
yurtable
UNION
SELECT
AcctNum,
"Ans4",
Ans4
FROM
yurtable
UNION
SELECT
AcctNum,
"Ans5",
Ans5
FROM
yurtable;

producing following thin dataset that will make it easy
to produce your wanted results

AcctNum Ans AnsVal
1 Ans1 yes
1 Ans2 no
1 Ans3 no
1 Ans4 N/A
1 Ans5 yes
2 Ans1 no
2 Ans2 N/A
2 Ans3 no
2 Ans4 no
2 Ans5 no

SELECT
AcctNum,
Abs(Sum(AnsVal='yes')) As YesCnt,
Abs(Sum(AnsVal='no')) As NoCnt,
Abs(Sum(AnsVal='N/A')) As NACnt
FROM
qryUnion
GROUP By
AcctNum;

 

[wal50]

Thanks to all for your help.
wal50

Meaning no offense, but it sounds like
you designed your tables like they were
Excel spreadsheets.....

If so, the "best" solution is to export them
to Excel and perform the counts there.

Please accept my apologies if I misunderstood,
but it sounds like you have table structure like:

AcctNum Ans1 Ans2 Ans3 Ans4 Ans5
1 yes no no N/A yes
2 no N/A no no no

if so, then it will serve you better to normalize
your data (possibly with UNION query like

qryUnion

SELECT
AcctNum,
"Ans1" As Ans,
Ans1 As AnsVal
FROM
yurtable
UNION
SELECT
AcctNum,
"Ans2",
Ans2
FROM
yurtable
UNION
SELECT
AcctNum,
"Ans3",
Ans3
FROM
yurtable
UNION
SELECT
AcctNum,
"Ans4",
Ans4
FROM
yurtable
UNION
SELECT
AcctNum,
"Ans5",
Ans5
FROM
yurtable;

producing following thin dataset that will make it easy
to produce your wanted results

AcctNum Ans AnsVal
1 Ans1 yes
1 Ans2 no
1 Ans3 no
1 Ans4 N/A
1 Ans5 yes
2 Ans1 no
2 Ans2 N/A
2 Ans3 no
2 Ans4 no
2 Ans5 no

SELECT
AcctNum,
Abs(Sum(AnsVal='yes')) As YesCnt,
Abs(Sum(AnsVal='no')) As NoCnt,
Abs(Sum(AnsVal='N/A')) As NACnt
FROM
qryUnion
GROUP By
AcctNum;