I know this is very basic, but I can be dense a lot of the time.

I have a report that shows the following:
Name, Department, Employee ID, Compliance 1, Compliance 2, Compliance 3,
Compliance 4.

In my report I need it to have a page break after each change in Department.
This I managed. But I also need it to calculate percentages. In each of
the Compliance columns the field is either Null or Yes. I need a percentage
of yes. In other words Countif(d2:d34)/count(c2:c34) Where d=Compliance 1
and c=Employee ID

I've added these to the group footer but no luck. I used the text box
feature--should I have used another feature to add them.

Maybe my formula is wrong.

Any ideas? I need a percentage for each of the four compliance columns.

Thanks.

Correct me if I have assumed wrong but "... Compliance 1, Compliance 2,
Compliance 3,
Compliance 4 " indicates a table that looks like:
TblEmployee
EmployeeID
Name
Employee ID
Department
Compliance1
Compliance2
Compliance3
If this is what you have (or similar) it is wrong and you should consider
redesigning your tables. This could be the underlying cause of your problem.

Steve
(E-Mail Removed)

"randlesc" <(E-Mail Removed)> wrote in message
news:1FF1CD0E-E604-4B56-B55C-(E-Mail Removed)...
>I know this is very basic, but I can be dense a lot of the time.
>
> I have a report that shows the following:
> Name, Department, Employee ID, Compliance 1, Compliance 2, Compliance 3,
> Compliance 4.
>
> In my report I need it to have a page break after each change in
> Department.
> This I managed. But I also need it to calculate percentages. In each of
> the Compliance columns the field is either Null or Yes. I need a
> percentage
> of yes. In other words Countif(d2:d34)/count(c2:c34) Where d=Compliance 1
> and c=Employee ID
>
> I've added these to the group footer but no luck. I used the text box
> feature--should I have used another feature to add them.
>
> Maybe my formula is wrong.
>
> Any ideas? I need a percentage for each of the four compliance columns.
>
> Thanks.

randlesc,

Not sure I get this... Countif(d2:d34)/count(c2:c34) Where d=Compliance 1
and c=Employee ID You would need to count Employee ID, count the Yes' and
do the math. But your *formula* indicates you are counting in and Excel
spreadsheet.

Which are you using Excel or Access?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
"randlesc" <(E-Mail Removed)> wrote in message
news:1FF1CD0E-E604-4B56-B55C-(E-Mail Removed)...
I know this is very basic, but I can be dense a lot of the time.

I have a report that shows the following:
Name, Department, Employee ID, Compliance 1, Compliance 2, Compliance 3,
Compliance 4.

In my report I need it to have a page break after each change in Department.
This I managed. But I also need it to calculate percentages. In each of
the Compliance columns the field is either Null or Yes. I need a percentage
of yes. In other words Countif(d2:d34)/count(c2:c34) Where d=Compliance 1
and c=Employee ID

I've added these to the group footer but no luck. I used the text box
feature--should I have used another feature to add them.

Maybe my formula is wrong.

Any ideas? I need a percentage for each of the four compliance columns.

Thanks.

That expression looks a lot like an EXCEL expression.

In Access, you could use an expression like the following in a CONTROL.
=Count([YourField])/Count(*)

Since Count counts any non-null value the Count([YourField]) will count the
number of Yes values. AND Count(*) counts the existence of a record.

IF you were trying to count YES and could have values such as "NO" or "Maybe"
then the expression could be like the following (among several variations)
=Count(IIF([YourField]="Yes",1,Null)/Count(*)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

randlesc wrote:
> I know this is very basic, but I can be dense a lot of the time.
>
> I have a report that shows the following:
> Name, Department, Employee ID, Compliance 1, Compliance 2, Compliance 3,
> Compliance 4.
>
> In my report I need it to have a page break after each change in Department.
> This I managed. But I also need it to calculate percentages. In each of
> the Compliance columns the field is either Null or Yes. I need a percentage
> of yes. In other words Countif(d2:d34)/count(c2:c34) Where d=Compliance 1
> and c=Employee ID
>
> I've added these to the group footer but no luck. I used the text box
> feature--should I have used another feature to add them.
>
> Maybe my formula is wrong.
>
> Any ideas? I need a percentage for each of the four compliance columns.
>
> Thanks.

Thanks for the reply.

I wasn't very clear. Its not a table. The report is constructed from a
query; and Employee ID is only used once.

"Steve" wrote:

> Correct me if I have assumed wrong but "... Compliance 1, Compliance 2,
> Compliance 3,
> Compliance 4 " indicates a table that looks like:
> TblEmployee
> EmployeeID
> Name
> Employee ID
> Department
> Compliance1
> Compliance2
> Compliance3
> If this is what you have (or similar) it is wrong and you should consider
> redesigning your tables. This could be the underlying cause of your problem.
>
> Steve
> (E-Mail Removed)
>
> "randlesc" <(E-Mail Removed)> wrote in message
> news:1FF1CD0E-E604-4B56-B55C-(E-Mail Removed)...
> >I know this is very basic, but I can be dense a lot of the time.
> >
> > I have a report that shows the following:
> > Name, Department, Employee ID, Compliance 1, Compliance 2, Compliance 3,
> > Compliance 4.
> >
> > In my report I need it to have a page break after each change in
> > Department.
> > This I managed. But I also need it to calculate percentages. In each of
> > the Compliance columns the field is either Null or Yes. I need a
> > percentage
> > of yes. In other words Countif(d2:d34)/count(c2:c34) Where d=Compliance 1
> > and c=Employee ID
> >
> > I've added these to the group footer but no luck. I used the text box
> > feature--should I have used another feature to add them.
> >
> > Maybe my formula is wrong.
> >
> > Any ideas? I need a percentage for each of the four compliance columns.
> >
> > Thanks.
>
>
> .
>

Sorry,

I tend to think naturally in Excel. But, I'm using Access.

"Gina Whipp" wrote:

> randlesc,
>
> Not sure I get this... Countif(d2:d34)/count(c2:c34) Where d=Compliance 1
> and c=Employee ID You would need to count Employee ID, count the Yes' and
> do the math. But your *formula* indicates you are counting in and Excel
> spreadsheet.
>
> Which are you using Excel or Access?
>
> --
> Gina Whipp
> 2010 Microsoft MVP (Access)
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
>
> http://www.regina-whipp.com/index_files/TipList.htm
> "randlesc" <(E-Mail Removed)> wrote in message
> news:1FF1CD0E-E604-4B56-B55C-(E-Mail Removed)...
> I know this is very basic, but I can be dense a lot of the time.
>
> I have a report that shows the following:
> Name, Department, Employee ID, Compliance 1, Compliance 2, Compliance 3,
> Compliance 4.
>
> In my report I need it to have a page break after each change in Department.
> This I managed. But I also need it to calculate percentages. In each of
> the Compliance columns the field is either Null or Yes. I need a percentage
> of yes. In other words Countif(d2:d34)/count(c2:c34) Where d=Compliance 1
> and c=Employee ID
>
> I've added these to the group footer but no luck. I used the text box
> feature--should I have used another feature to add them.
>
> Maybe my formula is wrong.
>
> Any ideas? I need a percentage for each of the four compliance columns.
>
> Thanks.
>
>
> .
>

Thanks. But how do I get four of these to show up in the group footer? I
can only seem to get one to show up.

Any ideas?



"John Spencer" wrote:

> That expression looks a lot like an EXCEL expression.
>
> In Access, you could use an expression like the following in a CONTROL.
> =Count([YourField])/Count(*)
>
> Since Count counts any non-null value the Count([YourField]) will count the
> number of Yes values. AND Count(*) counts the existence of a record.
>
> IF you were trying to count YES and could have values such as "NO" or "Maybe"
> then the expression could be like the following (among several variations)
> =Count(IIF([YourField]="Yes",1,Null)/Count(*)
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> randlesc wrote:
> > I know this is very basic, but I can be dense a lot of the time.
> >
> > I have a report that shows the following:
> > Name, Department, Employee ID, Compliance 1, Compliance 2, Compliance 3,
> > Compliance 4.
> >
> > In my report I need it to have a page break after each change in Department.
> > This I managed. But I also need it to calculate percentages. In each of
> > the Compliance columns the field is either Null or Yes. I need a percentage
> > of yes. In other words Countif(d2:d34)/count(c2:c34) Where d=Compliance 1
> > and c=Employee ID
> >
> > I've added these to the group footer but no luck. I used the text box
> > feature--should I have used another feature to add them.
> >
> > Maybe my formula is wrong.
> >
> > Any ideas? I need a percentage for each of the four compliance columns.
> >
> > Thanks.
> .
>

I've figured it out. Many thanks to all of you.

"randlesc" wrote:

> Thanks. But how do I get four of these to show up in the group footer? I
> can only seem to get one to show up.
>
> Any ideas?
>
>
>
> "John Spencer" wrote:
>
> > That expression looks a lot like an EXCEL expression.
> >
> > In Access, you could use an expression like the following in a CONTROL.
> > =Count([YourField])/Count(*)
> >
> > Since Count counts any non-null value the Count([YourField]) will count the
> > number of Yes values. AND Count(*) counts the existence of a record.
> >
> > IF you were trying to count YES and could have values such as "NO" or "Maybe"
> > then the expression could be like the following (among several variations)
> > =Count(IIF([YourField]="Yes",1,Null)/Count(*)
> >
> > John Spencer
> > Access MVP 2002-2005, 2007-2010
> > The Hilltop Institute
> > University of Maryland Baltimore County
> >
> > randlesc wrote:
> > > I know this is very basic, but I can be dense a lot of the time.
> > >
> > > I have a report that shows the following:
> > > Name, Department, Employee ID, Compliance 1, Compliance 2, Compliance 3,
> > > Compliance 4.
> > >
> > > In my report I need it to have a page break after each change in Department.
> > > This I managed. But I also need it to calculate percentages. In each of
> > > the Compliance columns the field is either Null or Yes. I need a percentage
> > > of yes. In other words Countif(d2:d34)/count(c2:c34) Where d=Compliance 1
> > > and c=Employee ID
> > >
> > > I've added these to the group footer but no luck. I used the text box
> > > feature--should I have used another feature to add them.
> > >
> > > Maybe my formula is wrong.
> > >
> > > Any ideas? I need a percentage for each of the four compliance columns.
> > >
> > > Thanks.
> > .
> >