Converting Yes/No to 1/0

[JeepsR4Mud]

I have a table called staff injuries. It has a check box field to not an outside referral to a hospital, etc. It is called OUTREF

Because I am interested in things like average number of accidents/week, etc. I also have a check box field called NONE. That way, the correct number of weeks are used in the average, because a record is created.

However, I think I need to create a "dummy" field for the OUTREF. When I use the report wizard to generate the SUM, AVG, MIN, and MAX, it always lists 1 as the MAX, which is the maximum number in one record. What I need is the MAX number in one week. I can sum the Yes/No in the report, but I can't use the AVG function with that calculated field.

So, it occurred to me that if I create a second field (which won't be displayed on my data entry form) where the values are generated from the OUTREF field. Something like if OUTREF is checked, the value in this field would equal 1, otherwise it is 0. I could then calculate my averages from this field.

Does that make sense?

I think I want to to this in a query.

Other suggestions welcome.

Thanks.

Gayle Ann

 

[Pat Hartman]

If the data type of the checkbox field is Yes/No, it will contain the value -1 for yes, 0 for no, or null if no value has been entered. You can calculate averages for this field. You can sum or average the value of the checkbox. If you want to convert it to a positive number, use the Abs() function.

Sum(Abs(YourField))

To calculate averages, you'll first need to sum the records to calculate a count for each week. You can then use that query in a totals query to calculate the average for a week.

Query1:
Select Format(YourDate,"yyyyww") As WeekNum, Sum(Abs(YourField)) As SumOfInjuries
From YourTable
Group By Format(YourDate,"yyyyww");

Query2:
Select Avg(SumOfInjuries) As AvgInjuries
From Query1;