Count Groups of Records

[depawl]

Hello. I'm trying to use a Query to count customer responses to a survey. For example, Customers can answer:
Yes, No, or Unsure
to the survey. I would like the Query to count total # of responses, total # of "Yes" responses, total # of "No" responses, and total # or "Unsure".
First of all, I don't understand why in Query Design View, When I select "Count" in the Total Category, and set the criteria as "Yes" it gives me a data type mismatch. These are just text fields in a table. I am not trying to sum them, just count them.
So I have been able to partially achieve what I want by using
SELECT Count
in SQL, but so far have had to use separate Queries for each category (Yes, No, or Unsure). I have tried
UNION SELECT with no success. Using separate Queries gets rather messy as there as several questions in this survey.
Thanks in advance for any assistance.

 

[DCrake]

First bring down the field you want to count the occurances by and select Group By then in the next empty column enter Cnt:1 and choose count. Next run the query to view results.



SELECT [<<YourField>>] , Count(1) As Cnt FROM <<YourTable>> GROUP BY [<<YourField>>]


David

 

[Brianwarnock]

Drag your field responses into the query grid in the next field enter

YesCount: IIf(responses ="yes",1,0)
continue for no and unsure,

select Totals query, from the dropdown on the Total row select Count for Resposnes and Sum for the other columns.
Run

Brian

 

[depawl]

Thanks. Either solution appears to do what I needed. Now the last step I need is a query to give me the percentages of each response.
For example, from the output of your query I have:
Yes 10
No 20
Maybe 10
So I need a query with the result to be:
Yes .25
No .50
Maybe .25
Once again, I've been able to accomplish this with 3 additional queries but would
like to be able to do it with one.
Thanks again.

 

[Brianwarnock]

My query would give
CountofResponses
countofyes
countofno
countofmaybe
therefore one simple query will give you what you need.

Brian