MS访问-使用带过滤器的DSUM运行总计的子查询

Question

为了在MS Access中生成Sales的运行总量，我使用了下面的查询，它正在按预期工作。

SELECT ID, [Product Line], DSUM("[Qty]","[SalesData]","[Product Line] like '*Electronics*' AND [ID] <=" & [ID]) AS RunningTotal, FROM SalesData WHERE ([Product Line]) Like '*Electronics*';

现在，我需要过滤RunningTotal < 100的所有记录，运行下面的子查询

SELECT * FROM( 
SELECT ID, [Product Line], DSUM("[Qty]","[SalesData]","[Product Line] like '*Electronics*' AND [ID] <=" & [ID]) AS RunningTotal, FROM SalesData WHERE ([Product Line]) Like '*Electronics*')
DSUM("[Qty]","[","[Product Line] like '*Electronics*' AND [ID] <=" & [ID]) < 100;

在运行此查询时，它无法工作，而且表多次被冻结。

数据表

ID    Product Line           Qty     RunningTotal
1     Electronics            15            15   
2     R.K. Electricals       20            20
3     Samsung Electronics    10            25
4     Electricals            30            50
5     Electricals            45            95
6     Electronics Components 18            43
7     Electricals            25           120
8     Electronics            50            93
9     Electricals Machines   65           185
10    Electronics            15           108
11    ABC Electronics Ltd    52           160
12    Electricals            15           200

这里， RunningTotal 是计算字段(非表场)，RunningTotal是不同的，而电子RunningTotal是不同的。

< 100的生产线电子的预期输出

ID    Product Line           Qty     RunningTotal
1     Electronics            15            15   
3     Samsung Electronics    10            25
6     Electronics Components 18            43
8     Electronics            50            93

请你帮我纠正上面的问题好吗？

提前谢谢。

Answer 1

与其使用众所周知速度慢的域聚合函数(如DSum)，我建议使用关联子查询，例如：

select q.* from
(
    select t.id, t.[product line], t.qty, 
        (
            select sum(u.qty) 
            from salesdata u 
            where u.[product line] = t.[product line] and u.id <= t.id
        ) as runningtotal
    from salesdata t
    where t.[product line] like "*Electronics*"
) q
where q.runningtotal < 100

编辑：

select t.*, q.runningtotal from salesdata t inner join
(
    select t.id, 
        (
            select sum(u.qty) 
            from salesdata u 
            where u.[product line] like "*Electronics*" and u.id <= t.id
        ) as runningtotal
    from salesdata t
) q on t.id = q.id
where q.runningtotal < 100 and t.[product line] like "*Electronics*"