Count from groups on a report

[Guest]

Hi all

I have a YTD report which is grouped by country, then by specialist and then
activity (it is based on a crosstab query which has a date range criteria to
get a specific year). I want a count for each country for the number of
specialists. If I use count([Specialist]) I get the number of activity rows
per country which isn't what I want. I also tried:

=DCount("[Specialist]","qry Resource Allocation
Prod","[CountryCode]=txtCountry")

but got the same result as Count([Specialist]). I want it to just count one
occurrence of Specialist per country - is this possible?

Thanks in advance for any help.
Sue

 

[Guest]

Found a solution thanks...

Put =1 in an unbound text field and set its running sum to over group then
referenced the name of this control in another control in the footer to be
able to use in calculations

 

[Guest]

mmm this worked fine until I tried to use the value in further calculations.
I added another unbound text control with control source of
=target*totalSpecCount and this made the totalSpecCount field to always
display 1 and not the running sum total of the group?

Anyone know a better way of achieving this?

Thanks

Found a solution thanks...

Put =1 in an unbound text field and set its running sum to over group then
referenced the name of this control in another control in the footer to be
able to use in calculations

Hi all

I have a YTD report which is grouped by country, then by specialist and then
activity (it is based on a crosstab query which has a date range criteria to
get a specific year). I want a count for each country for the number of
specialists. If I use count([Specialist]) I get the number of activity rows
per country which isn't what I want. I also tried:

=DCount("[Specialist]","qry Resource Allocation
Prod","[CountryCode]=txtCountry")

but got the same result as Count([Specialist]). I want it to just count one
occurrence of Specialist per country - is this possible?

Thanks in advance for any help.
Sue