如何在MS Access 365查询中从单位表提取乘数转换库存单位

核心逻辑 #

按要求，历史库存计算公式为：统一单位下的初始库存(Have) + 统一单位下的销量(Sold) - 统一单位下的采购量(Bought)，需先通过unit count表将各表的不同单位转换为统一基准单位（比如示例中的“件”）。

前提假设 #

假设各表结构如下：

• Have：ProductID（产品唯一标识）、HaveQty（库存数量）、HaveUnit（库存单位）
• Sold：ProductID、SoldQty（销售数量）、UnitSold（销售单位）、SoldDate（销售日期）
• Bought：ProductID、BoughtQty（采购数量）、BoughtUnit（采购单位）、BoughtDate（采购日期）
• unit count：ProductID、Unit（原单位）、Multiplier（转换为统一单位的乘数，比如统一单位为“件”时，吨对应的Multiplier为20）

完整查询SQL #

SELECT 
    p.ProductID,
    Nz(have.TotalHave, 0) + Nz(sold.TotalSold, 0) - Nz(bought.TotalBought, 0) AS HistoricalInventory
FROM 
    (
        -- 提取所有涉及的产品ID，避免遗漏
        SELECT DISTINCT ProductID FROM Have
        UNION
        SELECT DISTINCT ProductID FROM Sold
        UNION
        SELECT DISTINCT ProductID FROM Bought
    ) AS p
LEFT JOIN 
    (
        -- 转换Have表为统一单位并汇总
        SELECT 
            h.ProductID,
            SUM(h.HaveQty * uc.Multiplier) AS TotalHave
        FROM Have h
        INNER JOIN [unit count] uc 
            ON h.ProductID = uc.ProductID AND h.HaveUnit = uc.Unit
        -- 若Have是初始库存则无需日期筛选，若为动态库存则添加：WHERE h.RecordDate <= #2021-12-31#
        GROUP BY h.ProductID
    ) AS have ON p.ProductID = have.ProductID
LEFT JOIN 
    (
        -- 转换Sold表为统一单位并汇总（限定到2021-12-31及之前的销售）
        SELECT 
            s.ProductID,
            SUM(s.SoldQty * uc.Multiplier) AS TotalSold
        FROM Sold s
        INNER JOIN [unit count] uc 
            ON s.ProductID = uc.ProductID AND s.UnitSold = uc.Unit
        WHERE s.SoldDate <= #2021-12-31#
        GROUP BY s.ProductID
    ) AS sold ON p.ProductID = sold.ProductID
LEFT JOIN 
    (
        -- 转换Bought表为统一单位并汇总（限定到2021-12-31及之前的采购）
        SELECT 
            b.ProductID,
            SUM(b.BoughtQty * uc.Multiplier) AS TotalBought
        FROM Bought b
        INNER JOIN [unit count] uc 
            ON b.ProductID = uc.ProductID AND b.BoughtUnit = uc.Unit
        WHERE b.BoughtDate <= #2021-12-31#
        GROUP BY b.ProductID
    ) AS bought ON p.ProductID = bought.ProductID;

关键说明 #

1. 单位转换：通过INNER JOIN [unit count]匹配产品ID和单位，用数量 * Multiplier将不同单位转换为统一基准单位
2. 日期筛选：对Sold和Bought表添加<= #2021-12-31#的条件，确保只统计到目标日期为止的交易数据
3. 空值处理：用Nz()函数处理部分产品无初始库存/销售/采购记录的情况，避免计算结果为Null
4. 产品全集：通过UNION获取所有涉及的产品ID，确保不会遗漏任何产品的库存计算

注意事项 #

• 确保unit count表中每个产品的所有涉及单位都有对应的乘数记录，否则会导致该部分数据被排除
• 统一基准单位需提前确定，所有Multiplier均需基于该单位设置（比如统一为“件”时，“件”对应的Multiplier设为1）
• 若Have表是实时库存表而非初始库存，需添加日期筛选条件，只取2021-12-31当天的库存数据

内容的提问来源于stack exchange，提问作者UKlin