Unbound text

I tried to send this message before, but I think I've failed miserably.
Apologies if the same question apperas twice.

I have a report originated from a query. The query, among other info, lists
products and, next to them, producers. Of course, one producer can have
several products. In the report, I'm willing to show the total number of
products as well as the total number of producers. In the report I run a
=count(*) for the products and get the result I want. However, if I run the
same for producers, I get exactly the same amount, which double counts the
number of producers. I expect less producers than products. To have an
accurate number of producers I have, then, ran a second query listing and
grouping the producers to avoid the double count. However, I cannot make an
unbound text box in the report to read or count the results from this second
query. Can anyone pelase let me know what I'm doing wrong or an alternative
way of doing it?
Thanks,

Dom wrote:

I tried to send this message before, but I think I've failed miserably.
Apologies if the same question apperas twice.

I have a report originated from a query. The query, among other info, lists
products and, next to them, producers. Of course, one producer can have
several products. In the report, I'm willing to show the total number of
products as well as the total number of producers. In the report I run a
=count(*) for the products and get the result I want. However, if I run the
same for producers, I get exactly the same amount, which double counts the
number of producers. I expect less producers than products. To have an
accurate number of producers I have, then, ran a second query listing and
grouping the producers to avoid the double count. However, I cannot make an
unbound text box in the report to read or count the results from this second
query. Can anyone pelase let me know what I'm doing wrong or an alternative
way of doing it?


A better way is to have the repot group (View menu - Sorting
and Grouping) on the producer field. Put a text box (named
txtProducerCounter) in the group header or footer setion.
Set the text box's expression to =1 and RunningSum to Over
All. Then a report footer text box can display the number
of producers by using the expression =txtProducerCounter

--
Marsh
MVP [MS Access]

HI Marshall,

it didn't work. The txtproducercounter box showing =1 and running the sum
over all does show the number of producers as you said it would. However,
when I create the text box in the report header and make it equal to
txtproducercounter it shows 1 and not the sum of all 1's. Does it need to be
in the report footer to have the calculation happening or am I missing
something else?

Dom

"Marshall Barton" wrote:

Dom wrote:

I tried to send this message before, but I think I've failed miserably.
Apologies if the same question apperas twice.

I have a report originated from a query. The query, among other info, lists
products and, next to them, producers. Of course, one producer can have
several products. In the report, I'm willing to show the total number of
products as well as the total number of producers. In the report I run a
=count(*) for the products and get the result I want. However, if I run the
same for producers, I get exactly the same amount, which double counts the
number of producers. I expect less producers than products. To have an
accurate number of producers I have, then, ran a second query listing and
grouping the producers to avoid the double count. However, I cannot make an
unbound text box in the report to read or count the results from this second
query. Can anyone pelase let me know what I'm doing wrong or an alternative
way of doing it?


A better way is to have the repot group (View menu - Sorting
and Grouping) on the producer field. Put a text box (named
txtProducerCounter) in the group header or footer setion.
Set the text box's expression to =1 and RunningSum to Over
All. Then a report footer text box can display the number
of producers by using the expression =txtProducerCounter

--
Marsh
MVP [MS Access]

Dom wrote:
it didn't work. The txtproducercounter box showing =1 and running the sum
over all does show the number of producers as you said it would. However,
when I create the text box in the report header and make it equal to
txtproducercounter it shows 1 and not the sum of all 1's. Does it need to be
in the report footer to have the calculation happening or am I missing
something else?


It has to be in the report footer section. In some versions
of Access (A2003?) the report header can refer to the report
footer text box and get the total, but trying to refer to a
value before it has been calculated has always been an iffy
thing.

--
Marsh
MVP [MS Access]

Thanks, Marshall. It worked. I created a text box in the footer and another
one in the header, with the herader box referring to the footer one.
Thanks so much.
Dom

"Marshall Barton" wrote:

Dom wrote:
it didn't work. The txtproducercounter box showing =1 and running the sum
over all does show the number of producers as you said it would. However,
when I create the text box in the report header and make it equal to
txtproducercounter it shows 1 and not the sum of all 1's. Does it need to be
in the report footer to have the calculation happening or am I missing
something else?


It has to be in the report footer section. In some versions
of Access (A2003?) the report header can refer to the report
footer text box and get the total, but trying to refer to a
value before it has been calculated has always been an iffy
thing.

--
Marsh
MVP [MS Access]