访问SQL计算特定日期的库存，订购两次时库存结果翻倍

Question

我有一个SQL查询，可以在任何订单进出之前显示库存状态：

SELECT Query3.Products.ID, Query3.ProductName, Query3.StandardCost, Query3.OnHand,
  Nz(Sum([OrderJoin.Quantity]),0) AS Outgoing, Nz(Sum([Query1.Quantity]),0) AS Incoming,
  [OnHand]+[Outgoing]-[Incoming] AS OnHandAfter, [StandardCost]*[OnHandAfter] AS TotalCost
FROM Query3
GROUP BY Query3.Products.ID, Query3.ProductName, Query3.StandardCost, Query3.OnHand;

然后我尝试过滤它，使其接受用户输入的日期以回溯库存状态：

SELECT Q1.Products.ID, 
 Q1.ProductName, 
 Q1.StandardCost, 
 Q1.OnHand, 
 SUM([Q1.OnHand] + [Q2_sub.Outgoing] - [Q3_sub.Incoming]) AS OnHandAfter, 
 ([Q1.StandardCost] * [OnHandAfter]) AS TotalCost
FROM (
 Query3 AS Q1 
      LEFT JOIN (
           SELECT Q2.Products.ID, 
                Q2.ProductName, 
                Q2.StandardCost, 
                Q2.OnHand, 
                NZ(Sum([OrderJoin.Quantity]), 0) AS Outgoing 
           FROM Query3 AS Q2 
           WHERE (
                     (
                         (ShippedDate) > [Enter End Date]
                         OR (ShippedDate) IS NULL
                         )
                     )
           GROUP BY Q2.Products.ID, 
                Q2.ProductName, 
                Q2.StandardCost, 
                Q2.OnHand)  AS Q2_Sub ON Q1.Products.ID = Q2_Sub.ID) 
      LEFT JOIN (
                SELECT Q3.Products.ID, 
                     Q3.ProductName, 
                     Q3.StandardCost, 
                     Q3.OnHand, 
                     NZ(Sum([Query1.Quantity]), 0) AS Incoming
                FROM Query3 AS Q3 
                WHERE (
                     (
                         (DateReceived) > [Enter End Date]
                         OR (DateReceived) IS NULL
                      )
                 ) 
                GROUP BY Q3.Products.ID, 
                     Q3.ProductName, 
                     Q3.StandardCost, 
                     Q3.OnHand)  AS Q3_Sub ON Q1.Products.ID = Q3_Sub.ID
GROUP BY Q1.Products.ID, Q1.ProductName, Q1.StandardCost, Q1.OnHand;

它得出的结果大多是正确的，但也有一些是完全错误的，

编辑：

查看后，似乎有些在传出/传入列中翻了一番

编辑

编辑：

翻倍的产品的共同点是它们都是两次订购的。(即咖啡已经订购了一次25，一次订购5，但结果为60。如何通过两次计算它们来解决它？

还有像蟹肉这样的几个现在似乎在累积计算，即当我第一次运行它时，它显示为 370 OnHandAfter，现在在再次添加 416 后又出现 46

它应该是什么

| ID | Product Name | Standard Cost | On Hand | Outgoing | Incoming | OnHandAfter | TotalCost |
| 14 | Crab Meat    | £13.80        | 185     | 23       | 0        | 208         | £2,870.40

它来了

| ID | Product Name | Standard Cost | On Hand | Outgoing | Incoming | OnHandAfter | TotalCost
| 14 | Crab Meat    | £13.80        | 185     | 46       | 0        | 370         | £5,106.00

---

它应该是什么

| ID | Product Name | Standard Cost | On Hand | Outgoing | Incoming | OnHandAfter | TotalCost |
| 16 | Coffee       | £34.50        | 150     | 30       | 152      | 28          | £966.00

它来了

| ID | Product Name | Standard Cost | On Hand | Outgoing | Incoming | OnHandAfter | TotalCost
| 16 | Coffee       | £34.50        | 150     | 60       | 304      | 46          | £1,587.00

Answer 1

当您使用英镑时，您可能来自英国，因此日期格式为 dd/mm/yyyy。

因此，在查询中，您可能必须将[Enter End Date]指定为数据类型为 Date 的参数，并且 - 因为日期字段可以为 Null - 请注意这一点，例如替换以下内容：

(ShippedDate) > [Enter End Date]
OR (ShippedDate) IS NULL

跟：

Nz([ShippedDate], #9999/12/31#) > [Enter End Date]