getting unique counts

[4656rp]

I'm a novice to Crystal reports and need to create a report that counts the number of patients a doctor saw under several categories. For instance,

patient consultDr admitDr treatingDr therapyDr
001 DrABC DrABC DrABC DrXYZ
002 DrDEF DrABC DrDEF DrGHI
003 DrABC DrJKL DrDEF DrPQR

In this case DrABC would have a total count of 3 (pt 001, 002 and 003), the total count for DrDEF would be 2 (pt 002 and 003)and the total count for Drs GHI, JKL and PQR is 1.

In Access I create tables then use a distinct all union query.
Any ideas are greatly appreciated. Thank you.

 

[Madawc]

Try a cross-tab, category tabulated against doctor and counting patients. You'll find cross-tab under [insert] on the menu and it is very convenient for simple reports.

Madawc Williams (East Anglia, UK)

 

[lbass]

A union is probably the best solution here also. In 9.0 and above you can do this in "Add Command" and use it as your datasource. In 8.0 or 8.5, you can do this in the database->show SQL query, although you have to create an initial query since you can't modify the first part of the union statement, and then add the union queries after the where clause.

-LB