Count problem

  • Thread starter Thread starter SF
  • Start date Start date
S

SF

Hi,

I have a query that use for my report. This query contains the following:

Province District Commune
A AD1 AC1
A AD1 AC2
A AD1 AC3
B BD1 BC1
B BD1 BC2


I want to count how many provinces, distrcit and communes. In this case I
create 3 unbound filed and use Count(province), Count(District) and
count(Commune).
I result I got is 5 province, 5 districts and 5 communes which is wrong.
There are only two provinces here and 2 districts.

How can I address this issue?

SF
 
SF said:
I have a query that use for my report. This query contains the following:

Province District Commune
A AD1 AC1
A AD1 AC2
A AD1 AC3
B BD1 BC1
B BD1 BC2


I want to count how many provinces, distrcit and communes. In this case I
create 3 unbound filed and use Count(province), Count(District) and
count(Commune).
I result I got is 5 province, 5 districts and 5 communes which is wrong.
There are only two provinces here and 2 districts.
Click to expand...


Count counts all non-Null entries in the field, so 5 is the
expected result. Actually, I think this is what you want
for Commune, so that's a good way to get the Commune count.

Probably the easiest way to count unique entries is to use
Sorting and Grouping (View menu) to create groups with
header or footer for the Province and District. Add a text
box named txtProvinceCounter to the Province header/footer
section. Set its Control source expression to =1 and its
RunningSum property to Over All. Add a similarly configured
text box to the District header/footer. If you have no
other reason for the header/footer sections, you can make
them invisible.

Now, your report footer text boxes can use the expression:
=txtProvinceCounter
=txtDistrictCounter
 
Back
Top