Calculations in Group Footer
in [Getting Started]
|
From: randlesc on 27 Jan 2010 12:20 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" <randlesc(a)discussions.microsoft.com> wrote in message > news:1FF1CD0E-E604-4B56-B55C-3645A17A9A48(a)microsoft.com... > 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. > > > . >
From: randlesc on 27 Jan 2010 12:31 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. > . >
From: randlesc on 27 Jan 2010 12:53
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. > > . > > |