DISTINCT COUNT in a report 2

[kaymc]

I guess I already know the answer to this before I ask, it's just that I keep bumping up against the problem.

Why, oh why, couldn't they have implimented a distinct count function in vba? How hard could it have been, really? I mean Crystal has it!

Yes, I know I should handle it in my query, but sometimes that just isn't practical. Why can't I just add a field which does a distinct count of a given id field within a group. So unfair!

I don't suppose anyone has a clever workaround?

 

[BSman]

If you have sorted on a field and created a group footer for that field, then put code behind onPrint (or OnFormat) for the grouping footer that increments a variable by one each time the footer is printed. Then, at the bottom of the report, print the value of that variable. The one thing that gets tricky with this is that sometimes it works for OnFormat, other times you must use OnPrint because OnFormat will basically double the number.

What drives me nuts with Access reports is that I find the calculation of subtotals and totals is so unreliable that I have given up trying the running sum and similar options available and just calculate all subtotals and totals I need with VBA code. Frequently I'll do this in the details area of the report. It's not hard and seems to eliminate most of the uncertainty with the "built in" methods. In fact, it turns out to sometimes be simpler to use this method to calculate what would effectively be cross tab type reports than to create a cross tab query.

 

[dhookom]

It would help to know what you meant by a distinct count and how you wanted to implement it. If you have a report of several invoices with invoice details, using:
=Count([InvoiceID])
will count the total number of detail records which would probably not be what you want. However, if you add a text box in the Invoice Group Header section:
Name: txtInvCount
Control Source: =1
Running Sum: Over All
Visible: No
Then add a text box to your report footer:
Control Source: =txtInvCount
The final text box will display the number of distinct invoices.

Other distinct counts can be done with combinations of SQL totals queries and possibly subreports. Since you didn't provide more details, I am not sure with the above solution will work or not.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.