如何在MS Access中对字段求和

Question

我有2个表，有相同的字段，但数据不准确。我已经找到了价格上的差异，但是我如何将“差异”字段总结为另一个字段，以便我可以报告差异的总和。

SELECT 
a.barcode AS BarcodeSUS, 
a.sell AS PriceSUS, 
b.price AS PricePOS, 
a.sell-b.price AS difference
FROM SUS AS a LEFT JOIN POS AS b ON a.barcode = b.barcode
ORDER BY b.price;

Answer 1

如果您想要每行中的累加值，这应该可以实现以下目的：

SELECT
    a.barcode AS BarcodeSUS
    , a.sell AS PriceSUS
    , b.price AS PricePOS
    , a.sell-b.price AS difference
    , (SELECT
        SUM(SUS.sell-POS.price)
        FROM SUS
            LEFT JOIN POS ON SUS.barcode = POS.barcode
        WHERE SUS.barcode <= a.barcode
      ) AS [accumulated difference]
FROM SUS AS a
    LEFT JOIN POS AS b ON a.barcode = b.barcode
ORDER BY a.barcode;

如果您只想要总的差异：

SELECT
    a.barcode AS BarcodeSUS
    , a.sell AS PriceSUS
    , b.price AS PricePOS
    , a.sell-b.price AS difference
    , (SELECT
        SUM(SUS.sell-POS.price)
        FROM SUS
            LEFT JOIN POS ON SUS.barcode = POS.barcode
      ) AS [total difference]
FROM SUS AS a
    LEFT JOIN POS AS b ON a.barcode = b.barcode
ORDER BY a.barcode;

这里详细解释了“报表中的汇总”：https://support.office.com/en-gb/article/Summing-in-reports-ad4e310d-64e9-4699-8d33-b8ae9639fbf4?omkt=en-GB&ui=en-US&rs=en-GB&ad=GB