How to Count Individual Items in a Drop-down list

[balto1]

Hi All,

I have a drop down list with the values:
Contacted, Not Interested
Qualified, Opted Out
Screened, Waiting on Paperwork
Enrolled

I would like to run a query to generate a report that totals the number for each value ie how many people I have "Contacted, not interested," how many "qualified, opted out" etc.

For other drop down menus with just "yes" or "no" options (for example "contacted - yes or no"), I have used
=Count(IIf([Contacted]= yes, 1, null)) to total my responses. I don't understand how to do something similar for multiple values. Any help would be much appreciated!

[jimatqsi]

Using and extending your own methodology, you could do like this:

Select Count(IIf([Contacted]= yes, 1, null)) as Contacted, iif([ContactResult]="Contacted, Not Interested",1,null) as CNI, iif([ContactResult]="Qualified, Opted Out",1,null) as QOO, 
iif([ContactResult]="Screened, Waiting on Paperwork",1,null) as SWOP, iif([ContactResult]="Enrolled",1,null) as ENR ...
 

I believe that should give you counts for each of the values listed. I've made the assumption that the value you're counting is called "ContactResult"

Jim

[twinnyfo]

balto1,

Why not simply use an aggregate query, based on the dropdown values, with a Count on the Customer ID? jimatqsi's option would work if your dropdown values never changed and you wanted horizontal values. However, if you change the values of your dropdown, you would have to keep adding to the width of your query.

It just depends on how you want to manipulate your data afterwards.

[balto1]

Sorry for not responding sooner, I got caught up with things. Jim atqsi I did try your suggestion but kept an error message saying it contains invalid syntax, no matter how I try to change it.

twinnyfo could you please expand on how I to run an aggregate query - this sounds like exactly what I'm looking for!

Thanks!

[twinnyfo]

balto1,

When you create your Query, under the Query Tools Design Menu, there should be a Greek Sigma, with "Totals" underneath it. Click that, which will create an aggregate query.

Then, when you add your fields to the Query, for the field showing your contact information, there will be a "Total" row in the query designer. Select "Group By".

Then, add any other field from your table, but preferably the Customer ID (if you have one) because it's good practice to count the key field of a Table as a unique identifier. In the Query designer window, on the "Totals" Row, select "Count".

When you execute your query, you should have the following:

Customer Status                 CountOfCustomerID
Contacted, Not Interested       7
Qualified, Opted Out            12
Screened, Waiting on Paperwork  23
Enrolled                        48

I hope this makes sense. It's hard to teach in a one-sided post without showing and demonstrating.

Please let me know if you need any additional assistance!

[balto1]

So simple and exactly what I wanted; I don't know why I didn't figure this out sooner. Thank you SO much :D

[balto1]

One more question actually. If I have two or more field that I would like to count in a query using this method, for example I have "Recruitment Status" which has a drop down menu of answers and then I also have "Enrollment Status" with a different drop down menu of choices.

If I am counting my ID, and grouping by "Recruitment Status" is there a way to also separately count "Enrollment Status" in the same query? If i just add "Enrollment Status" into the query, its keeps the same count from "Recruitment Status." I hope this makes sense.Thanks!

[twinnyfo]

If you add Enrolled Status to your Query and Group by that Field, you will get something like this:

Recruitment Status               Enrollment Status   CountOfCustomerID
Contacted, Not Interested        Not Enrolled        23
Contacted, Not Interested        Enrolled            48
Qualified, Opted Out             Not Enrolled        15
Qualified, Opted Out             Enrolled            17

Aggregate queries can be very useful when you have different sets of datat like this, but they can also be tricky. It took me a lot of playing around with them to get good at making very complex queries that provided exactly what I wanted.

This should be a good place for you to start. Do some experimenting with this and let us know how it turns out.