如何在MS Access中获取总值或SUM（值）

Question

在MS Access中，我已连接到具有只读访问权限的外部Oracle数据库表。该表跟踪物品的生产过程（prodnr）。一篇文章可能会在一次或几次运行中生成，直到完成目标数量。

如何从该表中获取完整的AMOUNT（completekz = 1）或SUM（AMOUNT）（仅completekz = 0条目）。

PRODNR COMPLETEKZ BACOMPLETEKZ  AMOUNT 
------ ---------- ------------ ---------- 
    ...   ...     ... 
    16 0   partial    500 
    16 0   partial   20000 
    16 0   partial   8000 
    16 1   complete   40000 
    17 1   complete   100 
    18 0   partial   10000 
    18 0   partial   1500 
    19 1   complete   35000 
    ...   ...     ... 

这是我的查询，返回所有四行。我似乎无法找到正确的方法。

SELECT 
prodnr, 
completekz, 
bacompletekz, 
IIF (((SELECT MAX(sub1.completekz) FROM aforueck AS sub1 WHERE sub1.prodnr = '16') = '1'), 
    amount, 
    '-1' 
) AS Test 
FROM aforueck 
WHERE prodnr = '16' 

Answer 1

考虑

这

SELECT Q1.prodnr, Sum(IIf(([completekz]=1 And [MaxComp]=1) Or ([completekz]=0 And [MaxComp]=0),[Amount],0)) AS SumAmt FROM (SELECT aforueck.prodnr, aforueck.completekz, aforueck.bacompletekz, aforueck.amount, DMax("Completekz","aforueck","Prodnr='" & [prodnr] & "'") AS MaxComp FROM aforueck) AS Q1 GROUP BY prodnr;

或

SELECT aforueck.prodnr, Sum(IIf(([completekz]=1 And [MaxComp]=1) Or ([completekz]=0 And [MaxComp]=0),[Amount],0)) AS SumAmt FROM (SELECT aforueck.PRODNR, Max(aforueck.COMPLETEKZ) AS MaxComp FROM aforueck GROUP BY aforueck.PRODNR) AS Q1 INNER JOIN aforueck ON Q1.PRODNR = aforueck.PRODNR GROUP BY aforueck.prodnr;

都返回：
+--------+--------+ | prodnr | SumAmt | +--------+--------+ | 16 | 40000 | | 17 | 100 | | 18 | 11500 | | 19 | 35000 | +--------+--------+

这适合“complete AMOUNT（completekz = 1）或SUM（AMOUNT）（仅completekz = 0条目）”的描述。如果这不是所需的输出，请举例说明。