If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trying to Count
I am relatively new to building reports & using Access 2003. I am finding it
challenging to use some of the help menus online because I don't always know the terminology to use in order to ask the right questions. Here goes: I have a report that lists volunteers [SortName] and how many hours they have given for each department [RH], [IH], [JH]. The volunteers with the date & hours are grouped by the volunteer name [SortName]. In the group footer, I display the volunteer name and the sums of hours for each dept.(ie. =Sum[RH]; Sum[IH], Sum[JH]). In the report footer, I am able to display the sums for all individuals for each department. That works great. I also want to display a count of the number of individuals that gave hours in each department in the report footer. I have tried variations of DCOUNT trying to set a criteria (using IIF) to only count the individual if their sum of their hours for a given department are greater than zero. One of my challenges is that each individual may have given hours on more than one date but I only want to count that individual once. (Which is why I've trid basing the IIF expression on the sum being 0) At this point, I've tried so many different ways with no success that I'm getting a bit cross-eyed and just hoping that this explanation makes sense to someone. Thanks. |
#2
|
|||
|
|||
Trying to Count
BTW - The report is based on two queries: Volunteer & Hours.
Dragonfly wrote: I am relatively new to building reports & using Access 2003. I am finding it challenging to use some of the help menus online because I don't always know the terminology to use in order to ask the right questions. Here goes: I have a report that lists volunteers [SortName] and how many hours they have given for each department [RH], [IH], [JH]. The volunteers with the date & hours are grouped by the volunteer name [SortName]. In the group footer, I display the volunteer name and the sums of hours for each dept.(ie. =Sum[RH]; Sum[IH], Sum[JH]). In the report footer, I am able to display the sums for all individuals for each department. That works great. I also want to display a count of the number of individuals that gave hours in each department in the report footer. I have tried variations of DCOUNT trying to set a criteria (using IIF) to only count the individual if their sum of their hours for a given department are greater than zero. One of my challenges is that each individual may have given hours on more than one date but I only want to count that individual once. (Which is why I've trid basing the IIF expression on the sum being 0) At this point, I've tried so many different ways with no success that I'm getting a bit cross-eyed and just hoping that this explanation makes sense to someone. Thanks. |
#3
|
|||
|
|||
Trying to Count
Dragonfly -
If the source query for the report prints one record per volunteer, then the following will work. You will need to use the correct field name from your source data, and use the same approach for each department: =Sum(IIf(([HoursVolunteeredInRH])0,1,0)) If your source query has multiple records per volunteer, then you may want to tweak this query to contain the data you need. -- Daryl S "Dragonfly" wrote: BTW - The report is based on two queries: Volunteer & Hours. Dragonfly wrote: I am relatively new to building reports & using Access 2003. I am finding it challenging to use some of the help menus online because I don't always know the terminology to use in order to ask the right questions. Here goes: I have a report that lists volunteers [SortName] and how many hours they have given for each department [RH], [IH], [JH]. The volunteers with the date & hours are grouped by the volunteer name [SortName]. In the group footer, I display the volunteer name and the sums of hours for each dept.(ie. =Sum[RH]; Sum[IH], Sum[JH]). In the report footer, I am able to display the sums for all individuals for each department. That works great. I also want to display a count of the number of individuals that gave hours in each department in the report footer. I have tried variations of DCOUNT trying to set a criteria (using IIF) to only count the individual if their sum of their hours for a given department are greater than zero. One of my challenges is that each individual may have given hours on more than one date but I only want to count that individual once. (Which is why I've trid basing the IIF expression on the sum being 0) At this point, I've tried so many different ways with no success that I'm getting a bit cross-eyed and just hoping that this explanation makes sense to someone. Thanks. . |
#4
|
|||
|
|||
Trying to Count
There are multiple records for each volunteer. I understant the expression
you wrote below but can't figure out how to modify it for multiple records. Daryl S wrote: Dragonfly - If the source query for the report prints one record per volunteer, then the following will work. You will need to use the correct field name from your source data, and use the same approach for each department: =Sum(IIf(([HoursVolunteeredInRH])0,1,0)) If your source query has multiple records per volunteer, then you may want to tweak this query to contain the data you need. BTW - The report is based on two queries: Volunteer & Hours. [quoted text clipped - 24 lines] . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200912/1 |
Thread Tools | |
Display Modes | |
|
|