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.

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.

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.

.

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