Calculations in Group Footer

[randlesc]

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.

 

[Steve]

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 address removed)

 

[Gina Whipp]

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
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.

 

[John Spencer]

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]

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.

 

[randlesc]

Sorry,

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

 

[randlesc]

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?

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

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]

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

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?

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

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.

.