Cumulative Average in Query

[Kevin]

This is my first time visiting this forum, bare with me.

I have searched several places as well as Access help but have not
found the answer.

I need to calculate a 'cumulative average' in a query for trending.
For an example see the attached test file.

Any ideas?

Thanks!


+----------------------------------------------------------------+
| Attachment filename: example.txt |
|Download attachment: http://www.MSAccessForum.com.com/forums/attachment.php?postid=376346|
+----------------------------------------------------------------+

 

[Michel Walsh]

Hi,


"Web site cannot be found".


Generally, a running aggregate implies the same table twice.


SELECT a.CompanyID, a.dateTime, AVG(b.value)
FROM myTable AS a INNER JOIN myTable AS b
ON b.dateTime BETWEEN a.dateTime-60 AND a.dateTime
AND b.CompanyID = a.CompanyID

GROUP BY a.CompanyID, a.dateTime

would average on value for the 60 previous day, as example, per company.



Hoping it may help,
Vanderghast, Access MVP

 

[Kevin]

Vanderghast,

Thanks for the reply. I am struggling to follow your example for m
situation.

Did you see my example in the attached text file on the origina
message for this thread?

I appreciate it

 

[Kevin]

Vanderghast,

Thanks for the reply. I am struggling to follow your example for m
situation.

Did you see my example in the attached text file on the origina
message for this thread?

I appreciate it