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!