Aggregates in Queries Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to perform a "rolling average" within a query without dropping into VBA? I have a query that returns records sorted by date. For puposes of this discussion we'll assume that the query returns records with two fields - Date and Result. What I would like to get out of this is the average over Result for the current record and previous three records in the query (obviously the first 3 records in the query are an exception). Is there a way to do this without dropping into VBA

If it can't be done within a query, but can be done in a report that would be OK for this application (although not as convenient). Also the dates are randomly spaced but there is only one Result value per unique date. Grouping by date is not the right answer for this application - it really needs a "rolling average".

Thanks

dave
 
Is there a way to perform a "rolling average" within a query without dropping into VBA? I have a query that returns records sorted by date. For puposes of this discussion we'll assume that the query returns records with two fields - Date and Result. What I would like to get out of this is the average over Result for the current record and previous three records in the query (obviously the first 3 records in the query are an exception). Is there a way to do this without dropping into VBA?

If it can't be done within a query, but can be done in a report that would be OK for this application (although not as convenient). Also the dates are randomly spaced but there is only one Result value per unique date. Grouping by date is not the right answer for this application - it really needs a "rolling average".

Thanks.

dave
Click to expand...

It can be done with a Subquery: something like

SELECT [Date], [Result], (SELECT Avg([Result]) FROM yourtable AS X
WHERE X.[Date] IN (SELECT TOP 3 [Date] FROM yourtable AS Y
WHERE Y.[Date] < [yourtable].[Date] ORDER BY [Y].[Date])) AS
RollingAvg;
 
Back
Top