Results 1 to 3 of 3
  1. #1
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84

    Totals Problem in a grouped report


    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:
    Click image for larger version. 

Name:	report.PNG 
Views:	19 
Size:	25.5 KB 
ID:	34241

    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.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Put a hidden running sum in the grouping records footer, then in the footer put a sum of that.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    I tried your suggestion but couldn't get the result I wanted so I ended up creating a crosstab query where the counties were column headings and services and funding sources were row headings. That worked perfectly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-21-2016, 02:28 PM
  2. Report Grouped By First Leter Of a Name
    By WickidWe in forum Reports
    Replies: 4
    Last Post: 01-01-2014, 03:32 PM
  3. Grouped Report with Charts
    By FL_Boy in forum Reports
    Replies: 2
    Last Post: 12-21-2011, 09:27 PM
  4. Replies: 5
    Last Post: 12-06-2011, 11:18 AM
  5. Replies: 2
    Last Post: 08-25-2010, 01:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums