Results 1 to 6 of 6
  1. #1
    pgregory is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2018
    Location
    Minnesota
    Posts
    12

    Using a query to average values by month

    I have a query which I am trying to use to automatically calculate the average value of the field "DaysToIssue." The catch? I want to first group the records by another field, "MonthIssued," and find the average DaysToIssue for each group of records with the same MonthIssued.

    To illustrate, I've attached screenshots of my query in datasheet and design view. Currently, in datasheet view, Access shows every record. I would like to get it to collapse these records down so there is only one row shown for each month, with the corresponding average DaysToIssue, like so:

    MonthIssued DaysToIssue
    Nov 2017 26.5
    Dec 2017 34.5
    Jan 2018 1
    Feb 2018 52
    Mar 2018 14.75


    In design view, I tried putting "Avg" in the Total row for DaysToIssue, but that didn't seem to work, apparently because Access simply calculated the average for each record in isolation. Thanks for any help you can provide!


    Click image for larger version. 

Name:	DataSheetView.PNG 
Views:	13 
Size:	9.7 KB 
ID:	33629Click image for larger version. 

Name:	DesignView.PNG 
Views:	13 
Size:	5.8 KB 
ID:	33632

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    take out your first two columns

  3. #3
    pgregory is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2018
    Location
    Minnesota
    Posts
    12
    Ok, but if I do that, how would I sort MonthIssued chronologically (rather than alphabetically)? That's the purpose of the first two columns.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    format as yyyymm and sort on that,

  5. #5
    pgregory is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2018
    Location
    Minnesota
    Posts
    12
    Thanks, that works!

    One other question if you'd be so kind. I can't get the average of DaysToIssue to display as a whole number (zero decimal places) despite selecting that option in the Field Properties. Presumably this is because I'm only setting the decimal places property for the values from which the average is derived. Is there any way to control how many decimal places the average is displayed with?

  6. #6
    pgregory is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Mar 2018
    Location
    Minnesota
    Posts
    12
    Figured it out!

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

Similar Threads

  1. find average of values in a totals query
    By tagteam in forum Access
    Replies: 4
    Last Post: 11-07-2017, 03:43 PM
  2. Replies: 2
    Last Post: 01-25-2016, 10:25 AM
  3. Replies: 2
    Last Post: 04-29-2014, 03:04 AM
  4. Average percent by month query
    By jtmott in forum Queries
    Replies: 2
    Last Post: 11-05-2013, 10:44 AM
  5. Query to show AVERAGE of all values in a field
    By taimysho0 in forum Queries
    Replies: 9
    Last Post: 01-09-2012, 11:18 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