sql - MS Access 2010 查询多次提取相同的记录，sql 挑战

Question

我目前正在开发一个使用 ms Access 2010 跟踪我公司库存的程序。我很难获得旨在显示库存的查询以显示我想要的信息。问题似乎是查询多次提取相同的记录，从而夸大了保留和销售产品的总和。

背景:
我公司库存钢筋。我们提议将酒吧切成碎片。从库存方面，我们想要跟踪每个条的长度，从它进入仓库的那一刻起，到它在仓库中的时间(它可能被切成小块)，直到整个条被售出并消失.

数据库:
查询给出问题，查询3张表；

Barstock(具有以下字段)

BatchNumber(收到的所有条，属于同一生产热)

BarNo(个人酒吧)

原始长度(库存时收到的棒材长度

(BatchNumber 和 BarNo 结合，是主键)

销售量

ID(主键)

批号

酒吧号

售出数量

预约(卖家可以预约一些 Material ，当客户表示有兴趣，但需要时间来决定)

ID(主键)

批号

酒吧号

数量保留

我想将三个表中的信息提取到一个列表中，该列表显示:
-Barstock.orginial 收到时的长度
- Sales.Quantity 已售出
- 收到 - 按库存出售
- 保留。保留数量保留为保留
- 有库存 - 保留可用。

问题是我很擅长 sql。我已经尽我所能研究了联合和内联，但我的努力是徒劳的。我通常依靠设计 View 来生成我需要的 Sql 语句。在设计 View 中，我想出了以下 Sql:

SELECT 
  BarStock.BatchNo
  , BarStock.BarNo
  , First(BarStock.OrgLength) AS Recieved
  , Sum(Sales.QtySold) AS SumAvQtySold
  , [Recieved]-[SumAvQtySold] AS [On Stock]
  , Sum(Reservation.QtyReserved) AS Reserved
  , ([On Stock]-[Reserved])*[Skjemaer]![Inventory]![unitvalg] AS Available
FROM 
  (BarStock 
    INNER JOIN Reservation ON (BarStock.BarNo = Reservation.BarNo) AND (BarStock.BatchNo = Reservation.BatchNo)
  ) 
    INNER JOIN Sales ON (BarStock.BarNo = Sales.BarNo) AND (BarStock.BatchNo = Sales.BatchNo)
GROUP BY 
  BarStock.BatchNo
  , BarStock.BarNo

我知道查询多次提取相同的记录，因为；
- 当我删除 GROUP BY 术语时，我得到了几条完全相同的记录。
- 然而，在相应的表中只有这些记录的一个实例。

我希望我已经能够正确地解释自己，请询问我是否需要详细说明任何事情。

感谢您抽出时间来查看我的问题!

Answer 1

!!!检查一些假设

从您的数据库架构来看，似乎:

可能有多个 Sales给定的记录 BatchNumber/BarNo (例如，我可以想象多个客户可能购买了同一个酒吧的小节)。

可能有多个 Reservation给定的记录 BatchNumber/BarNo (例如，同一条的多个部分可以“保留”)

要检查这些表中是否确实有多个记录，请尝试以下操作:

SELECT CountOfDuplicates
FROM   (SELECT COUNT(*) AS CountOfDuplicates
        FROM   Sales
        GROUP  BY BatchNumber & "," & BarNo)
WHERE  CountOfDuplicates > 1 

如果查询返回一些记录，则存在重复项，这可能就是您的查询返回不正确值的原因。

从头开始

现在，让您的查询工作的诀窍是真正考虑您想要显示的主要数据是什么，然后从这里开始:

您基本上需要股票中所有柱的列表。
其中一些金条可能已售出，或者可能已被保留，但如果未售出，您应该显示可用库存数量。您当前的查询永远不会显示给您。

对于库存中的每个棒材，您希望列出已售数量和预留数量，并将它们组合起来以找出剩余的可用数量。

所以很明显，您的中心数据是库存条的列表。

与其尝试立即将所有内容都放入一个大型查询中，不如为每个目标创建简单的查询，并确保我们在每种情况下都能获得正确的数据。

只是酒吧

根据您的解释，每个单独的条都记录在 BarStock 中。 table 。
正如我在评论中所说，据我所知，所有交付的条形在 BarStock 中都有一个记录。表，无重复。因此，用于衡量库存的主要列表是 BarStock table :

SELECT BatchNumber, 
       BarNo, 
       OrgLength
FROM   BarStock

只是销售

同样，这应该非常简单:我们只需要找出每个 BatchNumber/BarNo 的总长度是多少。一对:

SELECT BatchNumber, 
       BarNo, 
       Sum(QtySold) AS SumAvQtySold
FROM   Sales
GROUP BY BatchNumber, BarNo

只是预订

与销售相同:

SELECT BatchNumber,
       BarNo,
       SUM(QtyReserved) AS Reserved
FROM   Reservation
GROUP  BY BatchNumber, BarNo 

原始库存与销售额

现在，我们应该能够将前 2 个查询合并为一个。我不是要优化，只是为了让数据协同工作:

SELECT BarStock.BatchNumber,
       BarStock.BarNo,
       BarStock.OrgLength,
       S.SumAvQtySold,
       (BarStock.OrgLength - Nz(S.SumAvQtySold)) AS OnStock
FROM   BarStock
LEFT JOIN (SELECT BatchNumber,
                  BarNo,
                  Sum(QtySold) AS SumAvQtySold
           FROM   Sales
           GROUP  BY BatchNumber, BarNo) AS S
  ON (BarStock.BatchNumber = S.BatchNumber) AND (BarStock.BarNo = S.BarNo) 

我们做了一个 LEFT JOIN因为可能有库存的金条尚未售出。
如果我们做了一个 INNER JOIN ，我们会在最终报告中遗漏这些，导致我们相信这些酒吧一开始就没有。

全部一起

我们现在可以将整个查询包装在另一个 LEFT JOIN 中反对保留的酒吧以获得我们的最终结果:

SELECT BS.BatchNumber,
       BS.BarNo,
       BS.OrgLength,
       BS.SumAvQtySold,
       BS.OnStock,
       R.Reserved,
       (OnStock - Nz(Reserved)) AS Available
FROM   (SELECT BarStock.BatchNumber,
               BarStock.BarNo,
               BarStock.OrgLength,
               S.SumAvQtySold,
               (BarStock.OrgLength - Nz(S.SumAvQtySold)) AS OnStock
        FROM   BarStock
        LEFT JOIN (SELECT BatchNumber,
                          BarNo,
                          SUM(QtySold) AS SumAvQtySold
                   FROM   Sales
                   GROUP  BY BatchNumber,
                             BarNo) AS S
          ON (BarStock.BatchNumber = S.BatchNumber) AND (BarStock.BarNo = S.BarNo)) AS BS
LEFT JOIN (SELECT BatchNumber,
                  BarNo,
                  SUM(QtyReserved) AS Reserved
           FROM   Reservation
           GROUP  BY BatchNumber,
                     BarNo) AS R
  ON (BS.BatchNumber = R.BatchNumber) AND (BS.BarNo = R.BarNo) 

注意使用 Nz()对于连接右侧的项目:如果没有 Sales或 Reservation给定数据 BatchNumber/BarNo对， SumAvQtySold 的值和 Reserved将是 Null并将呈现 OnStock和 Available null 同样，无论实际库存数量如何，这都不是我们期望的结果。

使用 Access 中的查询设计器，您必须分别创建 3 个查询，然后将它们组合起来。
请注意，查询设计不太擅长处理多个 LEFT和 RIGHT加入，所以我认为你不可能一口气写完整件事。

一些评论

我相信你应该阅读@Remou 在他的评论中给你的信息。
对我来说，这个数据库有一些不幸的设计选择:获取基本的股票数据应该很简单 SUM()在保存库存记录的列上。

通常，跟踪库​​存的一种简单方法是跟踪每笔库存交易:

进货记录有+数量

出库库存记录有-数量

该记录还应跟踪零件/项目/棒材引用(或 ID)、交易的日期和时间，以及 - 如果您想管理多个仓库 - 涉及哪个仓库 ID。

因此，如果您需要了解手头所有商品的完整库存，您只需要做的就是:

SELECT BarID,
       Sum(Quantity)
FROM   StockTransaction
GROUP BY BarID

在你的情况下，虽然 BatchNumber/BarNo是您的自然键，将它们保存在单独的 Bar 中table 会有一些优点:

您可以使用 Bar.ID取回 Bar.BatchNumber和 Bar.BarNo您需要的任何地方。

您可以使用 BarID作为您 BarStock 中的外键, Sales和 Reservation表。它使连接更容易，而不必弄乱复合键的复杂性。

Access 允许的一些东西并不是很好的做法，例如表名和字段中的空格，这最终会降低可读性(至少因为您需要将它们保持在 [] 之间)，与 VBA 变量名不太一致代表这些字段，并且与其他数据库不兼容，这些数据库除了表和字段名称的字母数字字符外不接受任何其他内容(如果您希望稍后扩大大小或将您的数据库与其他应用程序连接)。

此外，通过坚持单一的命名约定来帮助自己，并保持一致:

不要不一致地混合大写和小写:对于所有内容都使用 CamelCase，或小写或大写，但始终遵守该规则。

以单数或复数形式命名表格，但要保持一致。我更喜欢使用单数，比如 table Part而不是 Parts ，但这只是一个约定(这有其自身的原因)。

拼写正确:它是 Received不是 Recieved .在调试为什么某些查询或 VBA 代码不起作用时，仅仅因为有人打错字，这个错误可能会让您付出代价。

每个表应该/必须有一个 ID柱子。通常，这将是一个自动增量，以保证表中每条记录的唯一性。如果你保持这个约定，那么外键就变得容易猜测和阅读，而且你永远不必担心某些业务需求会改变你可能突然发现自己拥有 2 个相同的 BatchNumbers 的事实。 ，出于某种原因，您现在无法理解。

关于数据库设计有很多争论，但是每个人都同意某些“规则”，所以我的建议应该是争取:

简单性:确保每个表记录一种数据，不包含来自其他表的冗余数据(规范化)。

一致性:命名约定很重要。无论您选择什么，都要在整个项目中坚持下去。

清晰性:确保 3 岁后的您和其他人可以轻松阅读表名和字段并理解它们的含义，而无需阅读 300 页的规范。并不总是那么清楚，但这是值得努力的。