Counting and Running Sum

[holgi123]

Hi Everyone,

I would like to get some help on the following question. I have a report which is structured as follows:
===============
2003 2004 2005
State A

Rep1 $ 100 $ 35
Rep2 $ 15 $ 25
Rep3 $ 75 $ 43

State B
Rep4 $ 100 $ 35
Rep5 $ 15 $ 25
==============

Counting Reps by State I have meanwhile figured out with placing a field in the header, set the controlsource to 1 and than use Running sum "over group" and "overall" to count reps on state level or on the total report. So, in the above example State A has 3 reps and State B has 2 reps. No problem so far.

The problem I want to solve is count only reps per year who have generated revenue, i.e. State A would than have 2 reps for 2003 while State B would have 1 rep for 2003.....etc.

When I put a condition in the controlsource of the textbox - it does not work anymore and is all over the place.

Any help would be much appreciated.


Kind Regards

 

[THWatson]

holgi123
In your Group Footer, put an unbound text box and rather than Count use a Sum(Abs) structure as the control source.
For example =Sum(Abs([TheFieldYouWantToCount]))

Tom

 

[dhookom]

I agree with Tom. You might be able to use something like:
=Count([2003])
in the State footer.

I'm not sure why you are using a running sum to count Reps into a state header. All you should need is:
=Count(*)


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[holgi123]

Guys,

THWatson and dhookom, thank you for your help. I got things working. I probably forgot to mention that each sales rep has also subgroups being the products he/she sold.
The solution that finally worked was, that I put the following code into the unbound textbox's controlsource:

=iif(Sum([2003]<>0,1,0)

It now counts only the reps when they generated revenues.

Thank's again for your help and on to the next challenge I'm sure is just around the corner

Regards

 

[dhookom]

Your solution
=iif(Sum([2003]<>0,1,0)
can't work since it is missing a closing parens. It also looks like even a corrected expression might count the same rep multiple times for multiple products.

Are you sure your solution works?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[holgi123]

Dhookon,

you are correct, the closing parens was a typing mistake, as I did not paste the code. Apart from that the code seems to be correct. May be I explained things wrong. The report looks like:
2003
State A
Rep1
Prod i $ 50
Prod ii $120
Rep2
Prod i $ 0
Prod ii $ 0

State B
Rep3
Prod i $ 50
Prod ii $120
Rep4
Prod i $ 0
Prod ii $ 0

The report now shows for State A 1 Rep and for State B one rep. The report summary at the end shows 2 reps.
This is what I was after. Does it make sense?

Thank You again.

 

[dhookom]

If you expression in the State Footer is
=iif(Sum([2003]<>0,1,0))
then, State A and B each have two records with a 2003 value <>0. Is the Products records a subreport?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]