Results 1 to 9 of 9
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Combine and total records in a report

    Given a set of records within specified date range, I want to combine and total records of the same type. In the screenshot below, take for example the records that are in the category "Collected Dues". What I want is a single entry in the report for the category "Collected Dues" with a total of $585.00, the total of the 4 records shown. (The date field is no longer pertinent and will be removed from the report) So, the single line would read "Collected Dues" as the description, "Collected Dues" as the category and the amount $585.



    The reports "RecordSource" is a query that includes "CatID", which is the record ID in the table "Categories". The user will specify in the apps settings which categories are to be combined when reports are run and the app will query accordingly.

    Where does such functionality belong? Something in the queries SQL? Grouping?

    Click image for larger version. 

Name:	000.jpg 
Views:	20 
Size:	81.8 KB 
ID:	28279

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Create a grouping in the report, over Description (or Category - they seem pretty similar); if the Group, Sorting and Totals window isn't visible, open it via the ribbon Design tab.
    From that window, add a running sum total over the group, based on Amount.

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I grouped on category, but below is not at all what I'm trying to do.
    Click image for larger version. 

Name:	001.jpg 
Views:	21 
Size:	98.9 KB 
ID:	28280

    What I'm trying to do is combine and total specific categories. Like this: (Some of my crude graphic editing)
    Click image for larger version. 

Name:	002.jpg 
Views:	20 
Size:	51.7 KB 
ID:	28281

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So you're saying you don't want to show all the records for a group - you just want one record for a group with a total for that group? Then I think base your report on a Totals query, grouped by Category. Not sure how the records with no category will appear in that query but should be OK.

    Personally, I don't take a shine to that sort of report. A grand total in one record doesn't always paint a clear picture as to how the sum was arrived at. Any stray data that doesn't belong doesn't get noticed. You have to have complete faith that nothing out of the ordinary creeps in or gets left out if you don't have the details.

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Personally, I don't take a shine to that sort of report.
    Personally, I'm with you. If a registry record mistakenly gets miscategorized it will get buried in the group and potentially never get noticed. I'm going to try to convince the primary user of the app to abandon the idea or find an alternate approach to the desired results.

    Happy Easter!
    Thanks,
    Bill

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Now, if I could get the group totals to format and align correctly I think I'd be able to satisfy the requirement.

    Click image for larger version. 

Name:	003.jpg 
Views:	19 
Size:	78.6 KB 
ID:	28282

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Duh!

    Click image for larger version. 

Name:	003.jpg 
Views:	19 
Size:	109.8 KB 
ID:	28283

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The difference between the totals decimal points notwithstanding, I think I like a different justification. It jumps out more. Customer or personal preference I guess.

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Finished with some labeling and color highlighting:

    Click image for larger version. 

Name:	003.jpg 
Views:	18 
Size:	83.4 KB 
ID:	28285

    Thanks for the suggestions and helpful comments.
    Bill

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

Similar Threads

  1. Replies: 1
    Last Post: 04-06-2016, 09:26 AM
  2. Replies: 2
    Last Post: 04-22-2015, 12:28 PM
  3. Replies: 3
    Last Post: 01-18-2015, 06:05 PM
  4. Replies: 4
    Last Post: 03-04-2013, 05:00 PM
  5. Creating a percentage of total records report
    By sai_rlaf in forum Reports
    Replies: 3
    Last Post: 08-12-2011, 11:39 AM

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