Count only certain value

[jamie_b]

Hi,

I know this should be so easy, yet I have a mental block on this at the moment. Please help.

tbl_myTable:
Columns/Fields:

- pkID (primary auto),
- clientName (text),
- q1 (text) - can only be "ABC" or "DEF" (from form's radio button group),
- q2 (text) - can only be "ABC" or "DEF" (from form's radio button group),
- q3 (text) - can only be "ABC" or "DEF" (from form's radio button group)

Example:

PK= 1
clientName = John
q1 = ABC
q2 = ABC
q3 = DEF

Desired outcome of Query:

1
John
2 - (count of ABC for John only)

Help needed with this query:
- Irrespective of which column it is (ie. q1,q2,etc)...
- COUNT(sum?) only all the "ABC" for John.

Please help with Query Design Grid for this.

Thank you kindly,

Jamie.

 

[Galaxiom]

Your problem begins with an inappropriate table structure.

All responses should be stored in the same field. Another field stores the Q number.

Once this is done an aggregate query with a Group By on ClientName and Response fields along with a Count on any field will return the count of responses of each type for each ClientName.

 

[jamie_b]

Your problem begins with an inappropriate table structure.

All responses should be stored in the same field. Another field stores the Q number.

Thanks. I'm not sure I understand but here goes:

Table structure:
PK
clientName
question
response

Example:
Table_Row_1 = 1 | John | Q1 | ABC
Table_Row_2 = 2 | John | Q2 | ABC
Table_Row_3 = 3 | John | Q3 | DEF

Query result:
John ABC = 2
John DEF = 1

Is this what you mean ?

 

[Galaxiom]

That is basically correct.

Personally I would just use a number for the question.

BTW, you would normally use a ClientID rather than a ClientName. The name and other details about them would stored in a separate table with a relationship on ClientID.

 

[jamie_b]

Yes, I was going to get around to storing the client detail in a separate table.

Thanks for your help.