Running Sum and Sum of X-Consecutive Values

[Yolac]

FieldA contains values.
1.) How to create a query for FieldB where I want the
running sum of FieldA? 2.) How to create a query for
FieldC where I want the sum of 4 consecutive values of
FieldA? Example:
FieldC Row1=Row1 of FieldA
FieldC Row2=Row1+Row2 of FieldA
FieldC Row3=Row1+Row2+Row3 of FieldA;
FieldC Row4=Row1+Row2+Row3+Row4 of FieldA;
FieldC Row5=Row2+Row3+Row4+Row5 of FieldA;
FieldC Row6=Row3+Row4+Row5+Row6 of FieldA;
and so on.

Please help. Thanks.

 

[Michel Walsh]

Hi,


mTable ' table name
f1, f2, f3 ' fields name

f2 define the order (such as a date_time, for a transaction) and f3 some
"group" (ClientID, etc. )


SELECT a.f3, a.f2, SUM(b.f1)

FROM mtable As a INNER JOIN mTable As b
ON b.f3=a.f3 AND b.f2<=a.f2

WHERE b.f2 IN(
SELECT TOP 4 c.f2
FROM mTable As c
WHERE c.f3=a.f3 AND c.f2<=a.f2
ORDER BY c.f2 DESC)

GROUP BY a.f3, a.f2
ORDER BY a.f3, a.f2 ASC;



Hoping it may help,
Vanderghast, Access MVP