Running averages challenge..

  • Thread starter Thread starter Robert Cs.
  • Start date Start date
R

Robert Cs.

I have a form based on a simple table, where data gets inputted on a
real time basis, with the following fields: time and color value.
Example:
Time Color Value
12:11 10.12
12:34 10.34
12:36 10.1
12:45 9.56
12:47 9.89
12:53 9.97

etc..

As this form is open, I'd like to have another form open, that would
always show the running average of the last 4 Color values inputted.
So in this example, it would average 10.1, 9.56, 9.89 and 9.97..

I hope I described this challenge well, because I ran into a mental
block and can't come up with a solution and it frustrates a hell out
of me..

Can someone help me out?

Thank you in advance,
Rob
 
Hi,


Make a first query:

SELECT a.TimeStamp, a.ColorValue, COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.TimeStamp >= b.TimeStamp
GROUP BY a.TimeStamp


Save it as, say, q1.


SELECT a.TimeStamp, a.ColorValue, AVG(b.ColorValue)
FROM q1 As a INNER JOIN q1 As b
ON b.rank BETWEEN a.rank AND a.rank-3
GROUP BY a.TimeStamp



should return what you want.



Hoping it may help,
Vanderghast, Access MVP
 
(I changed Time into TimeStamp)

SELECT Avg(ColorValue) AS ColorAverage
FROM
(SELECT TOP 4 *
FROM YourTable
ORDER BY TimeStamp DESC);

Then, make sure you requery after each record update.

Tonín
 
Back
Top