Results 1 to 11 of 11
  1. #1
    Jblackbelt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    20

    Avg of Data instead of Groups


    Hi all,

    I am trying to create a report that shows turn around times of our process. I am able to sort it into groups and get an average of each of the groups, but i can't get a correct total average of all the data. If I use the average formula, it takes the average of the groups instead of all the data. Would someone be able to take a look at my turn around time report and provide me with some assistance? Also, if anyone knows how to add the group percentage of total, it would be greatly appreciated.

    Thanks in advanced!
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What should I enter into the input prompts?

    What would be the correct total in the report footer?

    Might want to reduce report width.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Jblackbelt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    20
    1/1/18 to 12/31/18 sorry about that

  4. #4
    Jblackbelt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    20
    Quote Originally Posted by Jblackbelt View Post
    1/1/18 to 12/31/18 sorry about that
    13.26 days is the correct answer

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Recommend not using an aggregate query as report RecordSource - use the Details query. Use report design to set grouping. When I tested this the report average is 13.26 instead of 14.22.

    You already have a Descrip group in the report. Put some textboxes in footer and use aggregate functions.

    Can set the Details section as not visible.


    Which total do you want percentage of?

    Give textboxes in group and report footers meaningful names, like tbxGrpLoanSum, tbxRptLoanSum. Then expression in a textbox in Group footer: =tbxGrpLoanSum/tbxRptLoanSum*100

    Or don't multiply by 100 and set textbox Format property to Percent.

    In your current report, textbox would be in Detail section and expression would be: =[SumOfCurr Prin Bal Amt]/[AccessTotalsSumOfCurr Prin Bal Amt]


    Strongly advise not to use spaces in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Jblackbelt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    20
    Thank you! This worked perfectly!

  7. #7
    Jblackbelt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    20
    What would be the best way to add sub groups. For example, if I want to have the main group be the credit risk reviewer, and then display the description days, what's the best way to do that?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Simply use the Sorting & Grouping tool and add a group for CreditRiskReviewer above the Descrip group.

    Move the sorting on SrtNr below the groups. Could use this field for grouping instead of Descrip. Sorting/filtering/indexing on numbers is supposed to be faster. Do that and won't need the sort criteria.

    Are you actually saving reviewer names instead of IDs?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Jblackbelt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    20
    Thanks. That worked perfectly as well. Yes, when we inherited the database, it was setup that way.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Might think about fixing that. Could improve performance and reduce file size.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Jblackbelt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2014
    Posts
    20
    Thanks! Definitely will look into it.

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

Similar Threads

  1. Add sequential numbering to groups of data
    By astephan in forum Access
    Replies: 8
    Last Post: 06-07-2018, 08:34 AM
  2. Replies: 6
    Last Post: 11-21-2014, 05:35 PM
  3. Replies: 15
    Last Post: 11-18-2013, 10:49 PM
  4. Report with Age groups data under each group
    By jyellis in forum Reports
    Replies: 1
    Last Post: 03-10-2013, 05:12 PM
  5. Option Groups and Adding Data to Tables
    By ipitydafool in forum Forms
    Replies: 5
    Last Post: 05-02-2011, 01:59 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