Count of fields

[jimb0ne]

hopefully a simple question...
I have 2 tables, one table is my main table, which stores doctor consultations, one field in said table is the name of the doctor who gave the consultation.

In my second table I have a list of the doctors names, and the type of doctor which they are, eg. obstetrician, oncologist, etc. These are not unique, eg. Dr. John smith and doctor james smith are both obstetricians.

what I want to be able to do is look at my main table, the list of consultations that is, and count the number of each TYPE of doctor that has given a consultation. So if 7 doctors who are all obstetricians have given 234 consulations between them, I want to be able to get that number using a query, or something to effect.

Thanks people,
Jimb0ne

 

[PHV]

A starting point:
SELECT B.[type of doctor], Count(*) As Consultations
FROM table1 A INNER JOIN table2 B
ON A.[name of the doctor] = B.[doctor name]
GROUP BY B.[type of doctor];

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