I have a report that lists every employee, the department they belong to, and 
their completion of certain annual competencies.

I have the report breaking at each change in department.  But, I would also 
like to include at the end of each section, the percentage compliance for 
that department.  In Excel I write =(COUNTIF(h2:h164, 
"Yes"))/(COUNT($C2:$C164) where column h is the compliance containg either 
"Yes" or a blank; and column C is the Employee Identification Number.

How do I put this into a report.  First, I cannot find how to make a group 
footer which I suppose is where this would go; second, should the formula be 
in the query? Someone said I would have to make a separate query and then add 
a sub-form.

Any suggestions. I'm sure this is pretty basic, but I've never had to write 
a report this complicated before (realizing its probably not complicated in 
the grand scheme or report writing).

Thanks.

Access reports have sorting and grouping which allows you to create group 
footer sections. In the group footer section, you can add a text box with a 
control source like:

   =Sum(Abs([H]=True))/Count([C])

H is the column/field containing a yes/no values recording completion.

If you can't figure this out, come back with some actual field names and how 
they are used.

-- 
Duane Hookom
Microsoft Access MVP


"randlesc" wrote:

> I have a report that lists every employee, the department they belong to, and 
> their completion of certain annual competencies.
> 
> I have the report breaking at each change in department.  But, I would also 
> like to include at the end of each section, the percentage compliance for 
> that department.  In Excel I write =(COUNTIF(h2:h164, 
> "Yes"))/(COUNT($C2:$C164) where column h is the compliance containg either 
> "Yes" or a blank; and column C is the Employee Identification Number.
> 
> How do I put this into a report.  First, I cannot find how to make a group 
> footer which I suppose is where this would go; second, should the formula be 
> in the query? Someone said I would have to make a separate query and then add 
> a sub-form.
> 
> Any suggestions. I'm sure this is pretty basic, but I've never had to write 
> a report this complicated before (realizing its probably not complicated in 
> the grand scheme or report writing).
> 
> Thanks.