Using conditional COUNT function

[Laura WW]

I apologize upfront ... it's been a REEEEEEALLY long time since I've worked with databases. I know there's a way to extract the info I need, but I can't figure out the code.

Background
I sent out a survey to customers, and then I imported their responses into Access. The table is not normalized. Certain survey questions allowed for more than one response. So, in those fields, I have more than one answer (e.g., "A B F").

Table name: tblSurveyResponses
Primary key: Email


Field name: Q3 (only one answer in field)
Field name: Q4 (has multiple responses)

What I want to accomplish
I want to count the various responses for each question. I assume that I need to put a function in a text box that says something like "If the answer to this question is 'A' then include it in the total count."

I also assume that, if I use a LIKE statement (e.g., [Q4] LIKE "*A*"), that will resolve my problem of multiple responses in a single field.

HELP! I know this can be done ... I just don't know how to write the code. I would be forever in your debt if someone could help me.

Thanks.

[June7]

Is the multi-response data all in the form "A B F" - 3 letters separated by spaces? How many letters could be used?

Probably several ways to do this.

In the Field row of query designer, for each data element, use an expression like:
DataA: IIf([Q4] Like "*A*", 1, Null)

[Laura WW]

In the multi-response fields, the letters are separated by a space and could contain up to 10 letters in some cases.

How do I incorporate the COUNT function into the expression you gave me?

[June7]

Will have to create a field for every possible letter response.

Two ways to get aggregate calcs in a table or query. With table or query in Datasheet view click the Totals (looks like Sigma) button on Home ribbon. This will present a Totals row at bottom of the query. Choose the desired function under each column. In Design view click the Totals button and select the keywords under each column on the Total row. Access Help has guidelines on Totals query.

Can also build a report and use its Grouping & Sorting with aggregate calcs functionaliy. Again, Access Help has guidelines.