counting amount of GROUPS, not the records in them..

[gangel]

Hey everyone,



So i have:


List of Customers
List of Items bought (by customers, using CustID)

I have these all on an invoice PER SHOP.

Therefore
SHOP:
Grouped by customer
Sorted by item name
sorted by item quantity


Now if i use =Count(*) it counts ALL the items sold, eg = 500
But i want to know how many customers have shopped at that store in this report.
eg. 50 customers

SO i really want to... count(CustID), but it still gives me 500!?

Ideas? Am i looking at this the wrong way??

[pbaldy]

Put a textbox in the group header with a control source of

=1

and a running sum of Over Group. Call it txtCount. In the report footer, put a textbox with a control source of

=txtCount

[gangel]

thanks pbaldy!

I finally had time to put aside and try this today

What i really need though is this data in the header!

Is there any way to use the =txtcount in the header instead?

[June7]

I tested. The answer is no. Accomplishing this would require some more elaborate method, not sure what - nested subquery, domain aggregate, subreport, VBA?

[gangel]

Thanks June. I ended up creating a subquery which i then use Dcount with and all work well

[June7]

Domain aggregate functions can perform slowly in queries. So if you start to notice slowness, this output should be possible with an aggregate query joined to the detail dataset.

Keep in mind that any filter applied to the report detail dataset must also be applied in the DCount or aggregate query.