Exclude an ID from a Group

[Chuck W]

Hi,
I have a table called tblPhysician with the fields PhysID, PhysName and
Speciality. There are about 1000 Physician and and about 12 Specialties.
There is also a table tblMedRecords that has PhysID and Mortality Counts for
2009. The field Mortality can have a count of zero or 1. I created a report
that sums the Mortality by Physician. What I want to do is to add another
field to report that sums Mortality by the specialty but excludes the
physician. I want my data to look like this:

PhysID PhysName Mortality Specialty SpecialtyMort
100 Jones 3 Cardiology 20
200 Smith 2 Cardiology 19

For Physician 100 I want to get a sum of all Mortalities for all
Cardilogists except him and call this SpecialtyMort (the 20 Cardiology
mortalities would exclude his 3). For Physician 200 I want to get again
exclude his mortalities and get all other Cardiologies including Phys 100.

Can someone help?

Thanks,
Chuck

 

[KARL DEWEY]

Try these --
qrySpecCount --
SELECT tblPhysician.Speciality, Sum(tblMedRecords.Mortality) AS SumOfMortality
FROM tblPhysician INNER JOIN tblMedRecords ON tblPhysician.PhysID =
tblMedRecords.PhysID
GROUP BY tblPhysician.Speciality;

SELECT tblPhysician.PhysID, tblPhysician.PhysName, tblMedRecords.Mortality,
tblPhysician.Speciality, [SumOfMortality]-[Mortality] AS SpecialtyMort
FROM (tblPhysician INNER JOIN tblMedRecords ON tblPhysician.PhysID =
tblMedRecords.PhysID) INNER JOIN qrySpecCount ON tblPhysician.Speciality =
qrySpecCount.Speciality;