sql - Access 查询 : Running total without using DSum (or another approach all together)?

Question

使用 DSum 计算我需要的数字需要花费天文数字的时间。我的查询似乎每行大约需要 0.1 秒，它有 4300 行。这意味着每次我刷新数据库时，大约需要 8 分钟...

我在这里遇到了很多问题，但如果你继续，它应该是完全连贯的。也就是说，如果您知道如何使用这两个表从 A 到 B 以及我想要的输出，请随时帮助我。我在下面有一个工作方法，但正如我所说，它花费的时间太长了。

我从下面的两个表格开始。我想做的是从我的需求数量中减去我的现有数量，并将任何继续超出的数量滚动到下一个日期。但是，我不想前滚我所需的数量。

在手

Product  |  QTY
   A     |  125

必需(在本例中，我每月需要 50 个)

Product  |  QTY Req  |  Date Req
   A     |     50    |   1-1-18
   A     |     50    |   2-1-18
   A     |     50    |   3-1-18
   A     |     50    |   4-1-18
   A     |     50    |   5-1-18
   A     |     50    |   6-1-18

而且，这是我想要的输出:(在这个例子中，我在 3 个月内用完了我的手头数量，之后每月仍然需要 50 个。

Product  |  Build QTY |  Date Req
   A     |      0     |   1-1-18
   A     |      0     |   2-1-18
   A     |      25    |   3-1-18
   A     |      50    |   4-1-18
   A     |      50    |   5-1-18
   A     |      50    |   6-1-18

---

---

第 1 步:

为我的现有数量分配一个错误的日期(手动设置为在任何要求的日期之前)。这样我们就可以进行联合查询以合并所有供需数量。

手头摘要(新查询)

SELECT
  DateValue("12/1/2017") AS [Date],
  [On Hand].[Product],
  [On Hand].[QTY];

第 2 步:

进行联合查询，以便我们可以获得所有日期↔产品组合。

uQuery(新查询)

SELECT 
  [On Hand Summary].[Date] AS [Date],
  [On Hand Summary].[Product] AS [Product]
  FROM [On Hand Summary]
UNION SELECT 
  [Required].[Date Req] AS [Date],
  [Required].[Product] AS [Product]
  FROM [Required];

第 3 步:

按产品和日期合并数量。在这里，我将现有数量乘以 -1，以便我们可以准备要前滚的数量。

我还将我的数量包装在 Iif(IsNull()) 中，这样我就可以用 0 替换空值。

Data Consolidation 1(新查询)

SELECT
  [uQuery].Date,
  [uQuery].Product,
  IIf(IsNull(-1*[On Hand Summary]![QTY]),0,-1*[OH Summary]![QTY]) AS [OH QTY],
  IIf(IsNull([Required]![QTY Req]),0,[Required]![QTY Req]) AS [Req QTY],
  [OH QTY]+[Req QTY] AS [Combined QTY]
FROM ([uQuery] 
  LEFT JOIN [On Hand Summary] ON 
    ([uQuery].Product = [On Hand Summary].Product) AND 
    ([uQuery].Date = [On Hand Summary].Date)) 
  LEFT JOIN Forecast ON 
    ([uQuery].Product = Required.Product) AND 
    ([uQuery].Date = Required.[Date Req]);

第 4 步(添加 DSum，耗时过长):

在这个查询中，我从第一个 Data Consolidation 查询中提取了 Product、Date 和 Combined QTY，并添加了一个 卷数列。

Data Consolidation 2(新查询)

Product  |   Date  |  Combined QTY  |  Roll QTY  
   A     | 12-1-17 |      -125      |    -125    
   A     |  1-1-18 |       50       |    -75     
   A     |  2-1-18 |       50       |    -25     
   A     |  3-1-18 |       50       |     25     
   A     |  4-1-18 |       50       |     75     
   A     |  5-1-18 |       50       |     125    
   A     |  6-1-18 |       50       |     175    

对于Roll QTY，我使用这个表达式:

Roll QTY: DSum("[QTY]","Data Consolidation 2","[Data Consolidation 1]![Product] = '" & [Product] & "' And [Data Consolidation 1]![Date] <= #" & [Date] & "#")

现在，这给了我我需要的东西，但正如我所说，每行大约需要 0.1 秒。跨越 4300 行(只会变得更大)，这是无法接受的计算时间。

第 5 步(未实现)

这部分我不需要帮助；我知道我需要做什么。但如果你想知道我将如何获得我想要的输出，我只是想包括这个。

我计划创建一个计算以下内容的最后一个查询

Minimum([Combined QTY], Maximum([Roll QTY],0))

这些是我理解的传统max 和min 函数I'll need to implement a VB module .

---

---

编辑:

我重新格式化了我的一些数据库，但我想我让它变得更简单了。我设法只制作了一个输入表，其中“现有”数量表示为负值。

Product  |  QTY Req  |  Date Req
   A     |    -125   |   12-1-18
   A     |     50    |   1-1-18
   A     |     50    |   2-1-18
   A     |     50    |   3-1-18
   A     |     50    |   4-1-18
   A     |     50    |   5-1-18
   A     |     50    |   6-1-18

有没有办法在不使用 DSUM 的情况下将其向前推进？

确认一下，最终值不能小于0，不能大于月数。

Answer 1

以下查询在子查询中计算运行总和，然后在外部查询中加入手头数量，并进行比较并返回结果，如我的评论中所述。

SELECT 
    rs.Product,
    SWITCH( 
        RunningSum - h.[QTY] < 0, 0, 
        RunningSum - h.[QTY] < rs.[QTY Req], rs.[QTY Req] - (RunningSum - h.[QTY]), 
        TRUE, rs.[QTY Req]
    ) AS [Build QTY],
    rs.[Date Req], RunningSum
FROM
  (SELECT
     (
        SELECT Sum(r.[QTY Req])
        FROM [Required] r
        WHERE r.[Date Req] <= o.[Date Req]
        AND r.[Product] = o.[Product]
        AND r.[QTY req] > 0
    ) AS RunningSum,
    o.Product,
    o.[QTY Req],
    o.[Date Req]
   FROM [Required] o
   WHERE o.[QTY req] > 0) rs
LEFT JOIN (SELECT oh.[QTY req]*-1 As QTY, Product FROM [Required] oh WHERE oh.[QTY req] < 0) h ON h.[Product] = rs.[Product]

---

解释:

计算运行总和的子查询是下面的查询:

SELECT Sum([QTY Req])
FROM [Required] r
WHERE r.[Date Req] <= o.[Date Req]
AND r.[Product] = o.[Product]
AND r.[QTY req] > 0

r和 o是此查询中的别名。 r是 required 的最内部实例 table 。 r是该表的外部实例。对于外部实例中的每一行，我正在计算产品代码相同的所有前几天的总和。

你可以使用 DSum而不是 SELECT Sum([QTY Req]) FROM [Required] r WHERE r.[Date Req] < o.[Date Req] AND r.[Product] = o.[Product] ，但这对性能有负面影响。

然后，在外部查询中，此子查询称为 rs , 我加入了 On Hand作为h (在 H 上，我已经使用了 o )并使用了我在评论中解释过的逻辑。