Adding percentages to groups in report

  • Follow


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.
0
Reply Utf 12/18/2009 8:55:01 PM

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.
0
Reply Utf 12/18/2009 9:11:01 PM


1 Replies
1311 Views

(page loaded in 3.919 seconds)


Reply: