Exclude an ID from a Group

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

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;

--
Build a little, test a little.


"Chuck W" wrote:

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

Chuck:

You can do this in the report by grouping the report on Specialty and giving
it a speciality group footer. As you are summing the mortality figures
already I'd guess the report is already grouped on Speciality and then on
Physician. In the speciality footer add a text box with a ControlSource of:

=Sum([Mortality])

Name the text box txtSpecialityMortalityTotal. You can set the text box's
Visible property to False (No) to hide it if you wish, or if you don't want
the footer to show at all set the group footer section's Visible property to
False.

In the header or footer section where you are currently summing the mortality
figures per physician add the SpecialtyMort text box and give it a
ControlSource property of:

= [txtSpecialityMortalityTotal] – Sum([Mortality])

That will subtract the current physician's total mortality figure for the
speciality from the overall total mortality figure for the speciality.

Ken Sheridan
Stafford, England

Chuck W wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201002/1