Counting multiple fields in a query

[Howard1024]

I have a program that logs OSHA safety report info. I have a query that pulls all records that involve an 'injury'. I need to count the number of 'yes' records for 5 other fields in this query and sum 2 other fields.

I tried doing separate queries on each counted field and entering the results by tying the recordsource to a text field. I get "error#' when I change view.

Suggestions.....please!

 

[wemeier]

You can do this all in one query. For the five fields that you want to count "yes" entries on, just add an expression to the QBE grid that assigns a value of 1 if the field is "yes", otherwise don't. Sum the results (the 1's will add up, the non-"yes" entries will not contribute to the total because they're zero. You can sum the other two fields in a similar manner. For example:

SELECT Sum(IIF([AmbulanceCalled]="yes",1,0)) AS AmbulanceCases, Sum(IIF([PersonInjured]="yes",1,0)) AS InjuryCases, Sum([WorkDaysLost]) AS WorkDaysLost FROM AccidentReports;

The first two fields "count" the number of cases where an ambulance was called and the number of cases where a person was injured by summing the 1's for all "yes" entries. The number of days lost for each report are also summed.

Cruising the Information Superhighway
_{(your mileage may vary)}