Calculations in Group Footer
in [Getting Started]
|
From: randlesc on 26 Jan 2010 19:01 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: Steve on 26 Jan 2010 19:24 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 santus(a)penn.com "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: Gina Whipp on 26 Jan 2010 20:43 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: John Spencer on 27 Jan 2010 08:46 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:19
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 > santus(a)penn.com > > "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. > > > . > |