query problem

[JohnE]

I am putting a query together that will eventually be for a report. But
getting lost in the process. I have 3 tables that are coming together to
form the query. A client can have many different areas that has revenue
attached to it. In the query/report only a field needs to show the sum of
the different areas, not each item. The different items would be totalled.
There is also another field that singles out only one item, of which, only
the revenue needs to be totalled for it. Below is what I have so far. It
does work, to a point. I am not able to get only the sum to work to display
in a field. Some of the fields are marked not to show.

SELECT usrtblClientInformation.ClientName,
usrtblClientInformation.ClientServicesPerson,
usrtblClientInformation.ClientRenewalDate,
usrtblAccountManagementUpdate.ProfitabilityMargin,
usrtblAccountManagementUpdate.RenewalStatus,
usrtblAccountManagementUpdate.RenewalIncrease,
usrtblAccountManagementUpdate.SpecialtyItemUpdateNote,
usrtblAccountManagementUpdate.SpecialtyItemTopic,
usrtblEstimatedRevenue.RevenueItem AS Revenue,
Sum(usrtblEstimatedRevenue.EstimatedRevenueAmount) AS
SumOfEstimatedRevenueAmount

FROM (usrtblClientInformation INNER JOIN usrtblAccountManagementUpdate ON
usrtblClientInformation.ClientCode =
usrtblAccountManagementUpdate.ClientCode)
INNER JOIN usrtblEstimatedRevenue ON usrtblClientInformation.ClientCode =
usrtblEstimatedRevenue.ClientCode

GROUP BY usrtblClientInformation.ClientName,
usrtblClientInformation.ClientServicesPerson,
usrtblClientInformation.ClientRenewalDate,
usrtblAccountManagementUpdate.ProfitabilityMargin,
usrtblAccountManagementUpdate.RenewalStatus,
usrtblAccountManagementUpdate.RenewalIncrease,
usrtblAccountManagementUpdate.SpecialtyItemUpdateNote,
usrtblAccountManagementUpdate.SpecialtyItemTopic,
usrtblEstimatedRevenue.RevenueItem,
usrtblEstimatedRevenue.RevenueYear

HAVING (((usrtblAccountManagementUpdate.SpecialtyItemTopic)="Renewal") AND
((usrtblEstimatedRevenue.RevenueYear)="2005"));

Thanks to anyone who responds.
*** John