Counting incident types

[jgnasser]

I have a table that has among others 4 fields that record whether or not an type of incident occured by use of a checkbox (I coulnt use a list because more than once type of incident can occur in a record).


Incitype1 Incitype2 Incitype3 Incitype4
Record1 Y Y N N
Record2 Y N Y N
Record3 N N Y N
Record4 N N Y N
........

(Y - checked, N - Not checked)

The result I need would go like this

Incitype1 2
Incitype2 1
Incitype3 3
Incitype4 0

I therefore have 4 fields each with a checkbox and what I need to do is count the number or each type of incident by counting the number of times the checkbox is 'Yes' for each record. I figure I need a query to do this but just can get it right.

 

[FoFa]

OK, there are many possible answers to your question, I will present one of simpler ones.
Create a query with a field to add your check boxes up. You just need to use the ABS function since true (YES) are -1 and false (NO) boxes are zero.
SELECT ABS(CB1) + ABS(CB2) + ABS(CB3) + ABS(CB4) as [Count of YES]

 

[jgnasser]

Thanks FoFa, even though this did not give me the exact solution I wanted, it prompted me to build the query I wanted.

The next issue I face is that my criteria will keep changing depending on the user input and I would like the query to read this criteria from a user form. I think it would be possible to write the query in code so that I have the flexibility of selecting criteria. Any idea on this or should I post it to VBA forum?

 

[FoFa]

By your example, you could use a crosstab query and you could specify the criteria as coming from a form rather than create a dynamic query. It would keep it simpler. Check the forum, or Access help on using Crosstab queries.