I've tried and I've tried and I've tried but I cant' figure this problem out. The report I am creating is an expense report. The report must show dollars spent on various services. The money spent on these services has several different funding sources. I need a report grouped by services and funding sources and split by the counties that the money was spent in. I've got everything on the report the way I want it to look but I can't get a total expenditure "by county". Here is an image of the report:
Here is the sql to get the report:
Code:
PARAMETERS Forms!Print_Client_reports_Menu.text2 DateTime;
SELECT qryBaseClients_List.Cl_County, qryBaseClients_List.Cl_Town, tblServicesHistory.Amount, tblServiceInstance.InDate, tblFundSource.FundSourceNm, tblServices.Svc_Name
FROM tblServices INNER JOIN ((qryBaseClients_List INNER JOIN tblServiceInstance ON qryBaseClients_List.ClID = tblServiceInstance.ID) INNER JOIN (tblFundSource INNER JOIN tblServicesHistory ON tblFundSource.F_ID = tblServicesHistory.Fund_ID) ON tblServiceInstance.Instance_ID = tblServicesHistory.ID) ON tblServices.Svc_ID = tblServicesHistory.Service_ID
WHERE (((tblServices.TrackExp)=True))
GROUP BY qryBaseClients_List.Cl_County, qryBaseClients_List.Cl_Town, tblServicesHistory.Amount, tblServiceInstance.InDate, tblFundSource.FundSourceNm, tblServices.Svc_Name;
The values I need for the report are the totals "by county"; there are only 2 counties to consider. First I tried to get an aggregate sum of each services type by performing a SUM of the named textboxes that hold the subtotals. That didn't work. Then I tried a union query but I cold't get the numbers that way either. Adding another grouping level by County sort of works, but I can't get the quantities to show up at the bottom of the columns in the report footer. I suspect that the answer is an easy one but I'm stumped.
Any help would be appreciated.