I think you can do something like...

select count(patient_id) from pateints p
left join results r on r.patient_id=p.patient_id
where r.result_id is null
group by patient_id

(above may not work, it is out of the head files) or

select count(p.patient_id) from patients p
where not exists (select patient_id from results r)

If you want the details just don't use count.

BOb



>From the Report from Hell files: I got a request to
>
>Are we currently reporting the # of patients that are NOT tested for something?
>
>Think in terms of a total # of patients =30
>result 1 = 15
>result 2 = 7
>Total = 22 && Group by Total
>NO TESTS = 8 && how do we generate this line in a SQL Statement ?????
>Total Pt = 30 && This is will be defined in a column in the table already
>
>
>This is all being done in SQL Server SP and will be reported with Crystal in a web interface.
>
>I just had a burst of ambition! Can you manipulate totals in Crystal reporting in the Group by footers? I find Crystal hard to manipulate probably out of ignorance.
>
>__Stephen