访问 2013 SQL，三个表，两个使用总和错误的结果

Question

有人可以帮我解决这个问题吗？我在互联网上搜索了几十个例子，但我就是找不到有效的解决方案。 我正在使用 Access 2013。问题是我正在尝试进行查询，该查询将突出显示来自有客户延期交货和/或逾期交货的供应商的所有零件号。

我正在使用三个表：

1. tbl_Inventory_Master我需要零件号、现有库存价值和供应商代码。
2. 对于任何延期交货，我需要加入 tbl_Customer_Back_Order表，因为我需要延期交货行的数量和延期交货数量的总和。
3. 如果供应商延迟交货，那么我需要添加显示逾期交货计数和逾期数量总和的tbl_On_Order表。

查询正在检索数据，但返回的数量是其应有数量的两倍。

SELECT
I.Inventory_Part_Num, I.Description, I.On_Hand_Stock,
COUNT (B.Part_Number) AS Back_Order_Count, SUM(B.Back_Order_Qty) as BO_Qty, 
COUNT(O.Part_Number) AS Late_Deliveries_Count, SUM(O.Order_Qty) AS Late_Qty
FROM (tbl_Inventory_Master AS I   
LEFT OUTER JOIN tbl_Customer_Back_Order AS B
ON I.Inventory_Part_Num = B.Part_Number)
LEFT OUTER tbl_On_Order AS O
ON I.Inventory_Part_Num = O.Part_Number
WHERE
I.Customer_Code = '274' AND
O.Due_Date < [ENTER TODAYS DATE IN FORMAT DD/MM/YYYY]
GROUP BY I.Inventory_Part_Num, I.Description, I.On_Hand_Stock

例如，对于零件号2022940我应该有 10 个延期交货行和 43 个逾期数量。相反，查询返回 20 个延期交货行和逾期数量总和 86。

从订单表中，我有三个订单，总计 144 件，而是查询返回 960。

有人可以建议吗，因为这让我发疯了？

Answer 1

您正在沿不相关的维度连接，因此您需要在连接之前进行聚合：

SELECT I.Inventory_Part_Num, I.Description, I.On_Hand_Stock,
B.Back_Order_Count, B.BO_Qty, 
O.Late_Deliveries_Count, O.Late_Qty
FROM (tbl_Inventory_Master AS I LEFT OUTER JOIN
(SELECT B.Part_Number, COUNT(*) as Back_Order_Count,
SUM(B.Back_Order_Qty) as BO_Qty
FROM tbl_Customer_Back_Order AS B
GROUP BY B.Part_Number
) as B
ON I.Inventory_Part_Num = B.Part_Number
) LEFT JOIN
(SELECT O.Part_Number, COUNT(O.Part_Number) AS Late_Deliveries_Count,
SUM(O.Order_Qty) AS Late_Qty
FROM tbl_On_Order AS O
WHERE O.Due_Date < [ENTER TODAYS DATE IN FORMAT DD/MM/YYYY]    
GROUP BY O.Part_Number
) as O
ON I.Inventory_Part_Num = O.Part_Number
WHERE I.Customer_Code = '274';

请注意，不再需要外部聚合。