count YES repsonses across a row and group

[eramirez]

Hi

I have a basic form that has TYPE and REASONS, 10 different reasons that can be checked. So each row will have a TYPE and the 10 different yes/not fields either checked or not. I want to run a query where it will group by TYPE and give me a count of all the YES responses per TYPE.

pretty new to access.

Thanks
Enrique

 

[ajetrumpet]

I can tell you are new. The data is not normalized. A normal setup for this would be two tables:

table 1: TYPES
table 2: RELATED fields to TYPES - child table of table 1 - one to many relationship.

The COUNT function in SQL usually refers to the vertical nature of the data in a matrix format. If you put the data in the format I have given you above, you can simply write the following in a query to give you the results that you want:

SELECT table1.type, COUNT(table2.reason) AS NumberOFyeses
   FROM table2 INNER JOIN table1 ON table1.type = table2.type
      GROUP BY table1.type
         HAVING table2.reason = -1;

 

[eramirez]

re:ajetrumpet

Thanks for the advice. I think I can set that up. What I have are 10 check boxes per row associated with TYPE. Each of these 10 check boxes have different field names (reas1-reas10). How do I include all 10 in the example query you gave?

Thanks again
Enrique

 

[ajetrumpet]

follow the setup i gave you. your "reasons" table will have two fields in it, and every TYPE will have 10 records. for example, your first 10 rows in the "reasons" table will look like this (in format of two fields)...

FIELD 1 FIELD 2
TYPE 1 reason 1
TYPE 1 reason 2
TYPE 1 reason 3
TYPE 1 reason 4
TYPE 1 reason 5
TYPE 1 reason 6
TYPE 1 reason 7
TYPE 1 reason 8
TYPE 1 reason 9
TYPE 1 reason 10

after you put the relationship in, the query can be run as i wrote above..