Offsetting an Average

[PogoP]

I'm relatively new to Access. I have a table with data against each month.
What I'm trying to do is work out an average of February through to July and apply this in October then an average of August through to January which would be applied in April.
Can anyone suggest the best way of doing this in Access?

01-Feb-18	450
01-Mar-18	425
01-Apr-18	476
01-May-18	450
01-Jun-18	479
01-Jul-18	520
01-Aug-18	523
01-Sep-18	435
01-Oct-18	475	466.67	Average
01-Nov-18	500
01-Dec-18	280
01-Jan-19	349
01-Feb-19
01-Mar-19
01-Apr-19		427.00	Average

[June7]

Won't be easy. Possibly involve DAvg() and/or VBA custom function.

[Micron]

If your table doesn't have a primary key field, you'll be making this more difficult, I think. Should you go the vba function route, the sql below might help with the calculation, but you'd have to figure out a way to input the date ranges. That could be a table of start and end dates, form values, array (less attractive I think) or other input. The sql as written could get you 2 values; Max of date and the average, then you'd have to find the record with that EXACT date (if you're using time, that might be a problem) and update the average.

However, it is advisable to NOT store calculations in a table, so this ought to be calculated on a form or report only.

Note - these are not formatted for vba but should be OK in sql design view. Dte is date/time field in table tblAvgData.

SELECT TOP 1 DMax("[Dte]","tblAvgData","[Dte] >=#02/01/2018# And [Dte]<= #07/01/2018#") 
AS MaxDte, DAvg("[amount]","tblAvgData","[Dte] >=#02/01/2018# And [Dte]<= #07/01/2018#") 
AS AvgAmt FROM tblAvgData;

If you do have an primary key ID field, then the update is safer in that the sql can return the ID of the record related to the calculation.

SELECT TOP 1 DMax("[ID]","tblAvgData","[Dte] >=#02/01/2018# and [Dte]<= #07/01/2018#") AS 
MaxID, DMax("[Dte]","tblAvgData","[Dte] >=#02/01/2018# and [Dte]<= #07/01/2018#") AS 
MaxDte, DAvg("[amount]","tblAvgData","[Dte] >=#02/01/2018# and [Dte]<= #07/01/2018#") AS AvgAmt
FROM tblAvgData;

[Peter M]

What is the overall goal you are trying to achieve? Is this an average that needs to be calculated repeatedly (and offset?) I.e. Next month will you need March-August and Sep-Feb?
If not and the averages are static (it will always be Feb-July and Aug-Jan) then you might consider adding a field called AvgGroup and populating it with Group1 for Feb-July and Group2 for Aug-Jan. Then you can just take the average where AvgGroup = Group1.

Also, when you say "Apply to October," what do you mean? Do you want to make a calculation with the result?

(Have you considered making a query that calculates the average where Date is Between #1-Feb-2018# and #31-Jul-2018#?)