Query: Apply Criteria On A Running Sum

[kculpepper77]

Based on a set of records <r> with data <x> which is grouped by attribute <y>; within each group of attribute <y> - i want to filter records as the running sum of data <x> exceeds criteria <z>.

for example, with the below data, as <x> exceeds <z> or 3.5 (by summing from top to bottom)- for each <y>, <r> is filtered accordingly.

y r x
- - -
a 1 1
a 2 2
a 3 4
a 4 2
b 1 1
b 2 5
b 3 6
b 4 2
c 1 4
c 2 4
c 3 2
c 4 8
c 5 4

so the query result would be like the below;

y r x
- - -
a 1 1
a 2 2
a 3 4
b 1 1
b 2 5
c 1 4

so could anyone support with how this could be applied in a access query. appreciate it.

 

[Remou]

At stab at it in two queries:
[tt]SELECT d.y, d.r, d.x, (Select Sum(x) From tblT z where d.y=z.y and z.r<=d.r ) AS Expr1, (Select Sum(x) From Imptxt z where d.y=z.y and z.r<d.r ) AS Expr2, IIf(([expr1]<3.5) Or ([expr1]>3.5 And [expr2]<3.5) Or ([expr1]>3.5 And [expr2] Is Null),[expr1],"") AS Expr3
FROM Imptxt AS d;[/tt]

[tt]SELECT qryQ.y, qryQ.r, qryQ.x, qryQ.Expr3
FROM qryQ
WHERE (((qryQ.Expr3)<>""));[/tt]