show records with multiple treatments

[din2005]

Hi a table treatment how do i show records with patient who had more than 1 treatment... i.e anyone who has same hospital no more than once e.g

hospno name treatdate
dd1234 andrew 12/12/2000
dd1234 andrew 12/12/2000
cc3246 david 20/01/1995
cc3246 david 20/01/1995

etc

is there a criteria i can use in the query to show records with more than one record using same hosp no?

 

[PHV]

Something like this ?
SELECT A.hospno, A.name, A.treatdate
FROM yourTable As A INNER JOIN (
SELECT hospno, name FROM yourTable GROUP BY hospno, name HAVING Count(*)>1
) As B ON A.hospno=B.hospno AND A.name=B.name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alvechurchdata]

Group the query by HospNo and add a Count(*) field. Then select those records where Count>1

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[din2005]

thanks guys

how would it look with this


SELECT TREATMENT.Hospital_no, PATIENTS.Surname, PATIENTS.firstname, TREATMENT.TREATDATE, TREATMENT.PATHOLOGY
FROM PATIENTS INNER JOIN TREATMENT ON PATIENTS.Hospital_no = TREATMENT.HOSP_NO
GROUP BY TREATMENT.Hospital_no, PATIENTS.Surname, PATIENTS.firstname, TREATMENT.TREATDATE, TREATMENT.PATHOLOGY
HAVING (((TREATMENT.PATHOLOGY)="trigeminal neuralgia"));


sorry queries/sql not my strongest points still learning...