Totalling Group totals - for grand total

[adam7]

Hi there, I'm using Crystal 8.5 with SQL 2000. The problem I have is gaining a count of unique contacts. I'll explain. I have one table for this report, which contains contacts. It has the company name against each contact, but each contact doesn't have a commen link other than the company name, which has to be spelt the same on each record for any kind of link (database limitations I'm afraid!)

What I'm trying to do is gain a total for each company, which is simple enough.

I create the report and have it grouped by company name. I then have a group total.....

Sprogings Batteries

Name Phone Email

Bob Smith 014436737 bob@sprog etc...
Ian Davis 3434434
Tom Letters 2323234

Total 3

At this point it's fine. However, if I have a list of say 100 companies, with 5 contacts per company, but some of the contacts do not have mail addresses I want to be able to count the COMPANIES (not individuals) that have at least one contact with a mail address.

I can get a count of mail addresses in the group, by putting in "if mailaddress is null then 1 else 0" type thing, which gives me the group total. However, I cannot then total THAT total for a grand total, and can't use it in a running total.

Any idea how I can count the number of companies which have at least one valid mail address??

Thanks

 

[lbass]

First create a formula {@hasaddress}:

if not isnull({Table.address}) then 1

Then insert a running total that does a distinctcount of {table.company}, evaluate using a formula:

sum({@hasaddress},{table.company}) > 0

Reset never.

-LB