Running total

H

hughess7

Hi all,
I have a report based on a crosstab query. I want to obtain the count of
'unique' specialists and then use this in further calculations on the report.
I have obtained the count of each specialists by putting =1 in an unbound
control (SpecCount) in the Specialist header, setting running sum to Over
Group and =SpecCount in a control (TotalSpecCount) in the footer.

If I then try to use TotalSpecCount in a calculation in another unbound
control SpecCount only ever has the value 1 in it therefore it stops working?

Does anyone know how I can do this so I can use the total number of
Specialists per Country (group) in calculations.

Thanks in advance for any help.
Sue
 
D

Duane Hookom

"a control (TotalSpecCount) in the footer" which footer?
"in a calculation in another unbound control" which section and what is the
calculation?
 
H

hughess7

Sorry...

A control source in the Country group footer.

Same section =[Target]*[totalSpecCount]

Target is the number of working days expected from a country. It is also an
unbound control with source:
=DLookUp("[TargetDays]","tblRates","Country=txtCountry")

Thanks

Thanks in advance for any help.
Sue
 
D

Duane Hookom

What/where is txtCountry from? I would expect to see something like:

=DLookUp("[TargetDays]","tblRates","Country=""" & txtCountry & """")

--
Duane Hookom
MS Access MVP
--

hughess7 said:
Sorry...

A control source in the Country group footer.

Same section =[Target]*[totalSpecCount]

Target is the number of working days expected from a country. It is also
an
unbound control with source:
=DLookUp("[TargetDays]","tblRates","Country=txtCountry")

Thanks

Thanks in advance for any help.
Sue


Duane Hookom said:
"a control (TotalSpecCount) in the footer" which footer?
"in a calculation in another unbound control" which section and what is
the
calculation?
Click to expand...
Click to expand...
 
H

hughess7

txtCountry is the Country which the report groups on, it is a control on the
report storing a bound text field in the Country header. It was working -
changed to your example which also works, but has no affect on the running
sum problem.

I have now found another solution from this newsgroup which does all the
calculations in code instead using instr function to increment count and
ignore duplicate specialists, which gets the TotalSpecCount per country. I
then have to do all my calculations in the format event of the country footer
section or I get the correct answers but randomly against the wrong
countries.

I think it is all working now - Thanks for trying to help though...

Duane Hookom said:
What/where is txtCountry from? I would expect to see something like:

=DLookUp("[TargetDays]","tblRates","Country=""" & txtCountry & """")

--
Duane Hookom
MS Access MVP
--

hughess7 said:
Sorry...

A control source in the Country group footer.

Same section =[Target]*[totalSpecCount]

Target is the number of working days expected from a country. It is also
an
unbound control with source:
=DLookUp("[TargetDays]","tblRates","Country=txtCountry")

Thanks

Thanks in advance for any help.
Sue


Duane Hookom said:
"a control (TotalSpecCount) in the footer" which footer?
"in a calculation in another unbound control" which section and what is
the
calculation?

--
Duane Hookom
MS Access MVP


Hi all,
I have a report based on a crosstab query. I want to obtain the count
of
'unique' specialists and then use this in further calculations on the
report.
I have obtained the count of each specialists by putting =1 in an
unbound
control (SpecCount) in the Specialist header, setting running sum to
Over
Group and =SpecCount in a control (TotalSpecCount) in the footer.

If I then try to use TotalSpecCount in a calculation in another unbound
control SpecCount only ever has the value 1 in it therefore it stops
working?

Does anyone know how I can do this so I can use the total number of
Specialists per Country (group) in calculations.

Thanks in advance for any help.
Sue
Click to expand...
Click to expand...
Click to expand...
 
Top