MS Access-使用带有筛选器的DSUM的运行总计子查询

问题描述

为了在MS Access中生成销售数量的运行总计，我在下面的查询中使用了它，它按预期工作

In order to generate running total of Sales Qty in MS Access, I used below query, it is working as expected

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，我运行了以下子查询

Now, I need to filter all the record with RunningTotal < 100, I ran the below sub query

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;

它不起作用，并且在运行该查询时表被冻结了很多次

It is not working and table is freezed many times while running this query

数据表

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不同

Here RunningTotal is calculated field (not table field)Electricals RunningTotal is different and Electronics RunningTotal is different

具有RunningTotal<的产品系列 like Electronics 的预期输出100

Expected output for Product Line like Electronics with 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

您能帮我解决以上问题吗?

Could you please help me to rectify the above query?

谢谢.

推荐答案

我建议使用相关的子查询，而不是使用众所周知的缓慢的域聚合函数(例如DSum)，而不是使用:

Rather than using domain aggregate functions (such as DSum) which are known to be notoriously slow, I would suggest using a correlated subquery, such as the following:

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*"

这篇关于MS Access-使用带有筛选器的DSUM的运行总计子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！