Results 1 to 15 of 15
  1. #1
    googalabosh is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    48

    Average two numbers


    I have a report that uses a line graph to calculate the average number of days to process a case per month. The problem is it appears to only be calculating this if it has three or more variables (closed cases) in that month.

    1) a query locates all cases which have been finished (query name: ClosedCases) and includes a column that calculates the number of days it took to close the case (Case closed date - Case open date). Column is labeled "Expr1"

    2) the report uses the query (ClosedCases) to display a line graph of the average number of days it takes to complete a case per month.

    3) the graph calculates the average in the report: Avg([Expr1]) AS [AvgOfExpr1] FROM [ClosedCases]

    I need it to calculate the average number of days to complete the case even if that month only has 1 or two closed cases.

    How do I get it to calculate the average with only two variables? TIA.
    Last edited by googalabosh; 04-17-2018 at 11:12 AM.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You need to post how this average is currently being calculated.
    Is that calculation happening in a Query first, or directly on the Report?
    What is the formula being used?
    If it is being done on the Report, in what Report section have you placed this calculated field?

  3. #3
    googalabosh is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    48
    Quote Originally Posted by JoeM View Post
    You need to post how this average is currently being calculated.
    Is that calculation happening in a Query first, or directly on the Report?
    What is the formula being used?
    If it is being done on the Report, in what Report section have you placed this calculated field?
    I apologise for that.

    1) a query locates all cases which have been finished (query name: ClosedCases) and includes a column that calculates the number of days it took to close the case (Case closed date - Case open date). Column is labeled "Expr1"

    2) the report uses the query (ClosedCases) to display a line graph of the average number of days it takes to complete a case per month.

    3) the graph calculates the average in the report: Avg([Expr1]) AS [AvgOfExpr1] FROM [ClosedCases]

    I need it to calculate the average number of days to complete the case even if that month only has 1 or two closed cases.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    OK, but in which Report section exactly have you placed the calculation and graph?
    Are you using any Grouping in the Report?

  5. #5
    googalabosh is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    48
    Quote Originally Posted by JoeM View Post
    OK, but in which Report section exactly have you placed the calculation and graph?
    Are you using any Grouping in the Report?
    It's in the "Detail" section of the report. I'm not sure what you mean by grouping.

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    The issue is that in the Detail section, it is working through the data and may not have gotten through all of it.
    The average needs to consider ALL of the data, in aggregate.
    So try putting your Average in one of the Footer sections of the report and see if it then works better.

  7. #7
    googalabosh is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    48
    For some reason the footer isn't showing. It is set to display always. Not sure what else I'm missing. It won't show in Report, Layout, or Print Preview. It does show in Design view.

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Have you placed anything visible in it?
    Note that there are multiple kinds of Footers; Report Footers, Page Footers, and Group Footers.

  9. #9
    googalabosh is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    48
    OK. I got it to show in the Report Footer, however, it still isn't calculating for months with less then 3 closed cases.

  10. #10
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    OK. I got it to show in the Report Footer, however, it still isn't calculating for months with less then 3 closed cases.
    This "for months with less than 3 closed cases" may be our key here.
    How/when exactly is the month that it is running on set/limited?
    Is it criteria on the "ClosedCases" query, or are you segregating the months out directly in the Report?
    If so, that is probably when you want to use Grouping, to Group on months, and put the calculations in the Group Footer.

    Also, you may want to watch this video on how to do Sums and Averages in Reports: https://www.youtube.com/watch?v=16R4YgNt_Rk

  11. #11
    googalabosh is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    48
    I'll just explain what I did and hope this helps.

    I inserted a line graph into the report. I set the graph to put the months on the x-axis and to average the number of days on the y-axis.

    I definitely can use that video for other things in the future but it doesn't talk about calculating averages in graphs.

  12. #12
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Sorry, I have never done graphs either. I thought you were just having trouble with the Average calculation.

  13. #13
    googalabosh is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    48
    That's OK. Thanks for trying.

  14. #14
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome. I am sorry I couldn't be more help.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    AFAIK, the entire graph has to be based on a table or query, so if you want an averages line, you need that data in the underlying data. But don't take my word for it; Google it because I'm not real familiar with Access graphs either. I gave up on them long ago, even for the simplest of graphs because I found them buggy and real aggravating trying to get them to work right. Access wasn't really meant to create great graphs, and never will.

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

Similar Threads

  1. Average on a Group Average
    By Lykins in forum Reports
    Replies: 2
    Last Post: 04-24-2017, 01:28 PM
  2. Graphing the average of a set of numbers
    By GoBlue in forum Reports
    Replies: 1
    Last Post: 03-05-2015, 01:22 PM
  3. Replies: 2
    Last Post: 04-29-2014, 03:04 AM
  4. Replies: 8
    Last Post: 03-10-2014, 11:47 AM
  5. Replies: 13
    Last Post: 05-28-2010, 11:57 AM

Tags for this Thread

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