Distinct Count within Report

[andthe]

I have a report with 2 groups: PART and VENDOR. An example of the output is belows:

PART VENDOR PO
abc target a12345
abc target a32343
abc kmart b23433
def walmrt c23433

Using the example above, I would expect to see a vendor total of 2 for part ABC and a vendor total of 1 for part DEF. This sounds easy, but I cannot determine how to perform the calculation within the report. Any help is greatly appreciated.

 

[Joe4]

Are you actually using Groups in your report? If so, you should be able to use the Group Footer to calculate totals for that specific group. Simply insert a text box and in the Control Source enter:

=COUNT([FieldName]) to get the count and
=SUM(FieldName]) to get sthe sum of each group.

If you want a Grand Total of all groups, simply enter these expressions in Text Boxes in the Report Footer.

 

[andthe]

Yes, I'm am using Groups. I verified this by going to View --> Sorting and Grouping and I see a symbol next to both of those fields. I attempted to use the count formula, but I instead of getting of a total of 2 vendors for part ABC I'm getting 3. It's counting each record it finds within that group - not the unique records. Am I missing something? Thanks!

 

[Joe4]

I would createanother query to count the distinct number of Vendors for each part.

Simply do a query where you add the Part field, then the Vendor field twice. Then clicking on the Totals button, so that "Group By" appears under each field. On the second Vendors field, change the "Group By" to "Count".

Then link this query into your main query the report is running off of, and you will have access to the count you need.

There may be a better way to do it, i.e. in a calculation in the report, but I am unsure how to do it.