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

MS Access 2010 query pulls same records multiple times, sql challenge

提问by Rookie

I'm currently working on a program that keeps track of my company's stock inventory, using ms Access 2010. I'm having a hard time getting the query, intended to show inventory, to display the information I want. The problem seems to be that the query pulls the same record multiple times, inflating the sums of reserved and sold product.

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

Background: My company stocks steel bars. We offer to cut the bars into pieces. From an inventory side, We want to track the length of each bar, from the moment it comes in to the warehouse, through it's time in the warehouse (where it might get cut into smaller pieces), until the entire bar is sold and gone.

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

Database: The query giving problems, is consulting 3 tables;

数据库：给出问题的查询，正在查询3张表；

• Barstock (with the following fields)
  • BatchNumber (all the bars recieved, beloning to the same production heat)
  • BarNo (the individual bar)
  • Orginial Length (the length of the bar when recieved at the stock

• 棒料（具有以下字段）
  • BatchNumber（收到的所有条形，属于同一生产热度）
  • BarNo（个人酒吧）
  • 原始长度（库存时收到的棒材长度

(BatchNumber and BarNo combined, is the primary key)

（BatchNumber 和 BarNo 结合，是主键）

• Sales

  • ID (primary key)
  • BatchNumber
  • BarNo
  • Quantity Sold

• Reservation (a seller kan reserve some material, when a customer signals interest, but needs time to decide)

  • ID (Primary key)
  • BatchNumber
  • BarNo
  • Quantity reserved

• 销售量

  • ID（主键）
  • 批号
  • 酒吧号
  • 销售数量

• 预约（卖家可以预约一些材料，当客户表示有兴趣，但需要时间来决定）

  • ID（主键）
  • 批号
  • 酒吧号
  • 预留数量

I'd like to pull information from the three tables into one list, that displays: -Barstock.orginial length As Received - Sales.Quantity sold As Sold - Recieved - Sold As On Stock - reservation.Quantity Reserved As Reserved - On Stock - Reserved As Available.

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

The problem is that I suck at sql. I've looked into union and inner join to the best of my ability, but my efforts have been in vain. I usually rely on the design view to produce the Sql statements I need. With design view, I've come up with the following Sql:

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

I know that the query is pulling the same record multiple times because; - when I remove the GROUP BY term, I get several records that are exactley the same. - There are however, only one instance of these records in the corresponding tables.

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

I hope I've been able to explain myself properly, please ask if I need to elaborate on anything.

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

Thank you for taking the time to look at my problem!

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

回答by Renaud Bompuis

!!! Checking some assumptions

！！！检查一些假设

From your database schema, it seems that:

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

• There couldbe multiple Salesrecords for a given BatchNumber/BarNo(for instance, I can imagine that multiple customers may have bought subsections of the same bar).
• There couldbe multiple Reservationrecords for a given BatchNumber/BarNo(for instance, multiple sections of the same bar could be 'reserved')

• 有可能是多个Sales记录，对于给定的BatchNumber/BarNo（例如，我可以想像，多个客户可能具有相同的吧买小节）。
• 有可能是多个Reservation记录，对于给定的BatchNumber/BarNo（例如，同一棒的多个部分，可以“保留”）

To check if you do indeed have multiple records in those tables, try something like:

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

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

If the query returns some records, then there are duplicates and it's probably why your query is returning incorrect values.

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

Starting from scratch

从头开始

Now, the trick to your make your query work is to really think about what is the main data you want to show, and start from that:

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

• You basically want a list of all bars in the stock. Some of these bars may have been sold, or they may be reserved, but if they are not, you should show the Quantity available in Stock. Your current query would never show you that.
• For each bar in stock, you want to list the quantity sold and the quantity reserved, and combined them to find out the quantity remaining available.

• 您基本上需要股票中所有柱的列表。其中一些金条可能已售出，或者可能已被保留，但如果未售出，您应该显示可用库存数量。您当前的查询永远不会显示给您。
• 对于库存中的每个棒材，您希望列出已售数量和预留数量，并将它们组合起来以找出剩余的可用数量。

So it's clear, your central data is the list of bars in stock.

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

Rather than try to pull everything into a single large query straight away, it's best to create simple queries for each of those goals and make sure we get the proper data in each case.

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

Just the Bars

只是酒吧

From what you explain, each individual bar is recorded in the BarStocktable.
As I said in my comment, from what I understand, all bars that are delivered have a single record in the BarStocktable, without duplicates. So your main list against which your inventory should be measured is the BarStocktable:

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

SELECT BatchNumber, 
       BarNo, 
       OrgLength
FROM   BarStock

Just the Sales

只是销售

Again, this should be pretty straightforward: we just need to find out how much total length was sold for each BatchNumber/BarNopair:

同样，这应该非常简单：我们只需要找出每BatchNumber/BarNo对售出的总长度：

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

Just the Reservations

只是预订

Same as for Sales:

与销售相同：

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

Original Stock against Sales

原始库存与销售额

Now, we should be able to combine the first 2 queries into one. I'm not trying to optimise, just to make the data work together:

现在，我们应该能够将前 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) 

We do a LEFT JOINbecause there might be bars in stock that have not yet been sold.
If we did an INNER JOIN, we wold have missed these in the final report, leading us to believe that these bars were never there in the first place.

我们这样做是LEFT JOIN因为可能有库存的金条尚未售出。
如果我们做了一个INNER JOIN，我们就会在最终报告中错过这些，这让我们相信这些酒吧一开始就不存在。

All together

全部一起

We can now wrap the whole query in another LEFT JOINagainst the reserved bars to get our final result:

我们现在可以将整个查询包装在另一个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) 

Note the use of Nz()for items that are on the right side of the join: if there is no Salesor Reservationdata for a given BatchNumber/BarNopair, the values for SumAvQtySoldand Reservedwill be Nulland will render OnStockand Availablenull as well, regardless of the actual quantity in stock, which would not be the result we expect.

使用注意事项Nz()对于那些上的右侧项目加盟：如果没有Sales或Reservation数据对于一个给定BatchNumber/BarNo对，价值观SumAvQtySold和Reserved会Null，并会呈现OnStock和Available库存的实际数量的空以及不分，这不会是我们期望的结果。

Using the Query designer in Access, you would have had to create the 3 queries separately and then combine them.
Note though that the Query Designed isn't very good at dealing with multiple LEFTand RIGHTjoins, so I don't think you could have written the whole thing in one go.

使用 Access 中的查询设计器，您必须分别创建 3 个查询，然后将它们组合起来。
请注意，查询设计不太擅长处理多个LEFT和RIGHT连接，因此我认为您不可能一次性编写整个内容。

Some comments

一些评论

I believe you should read the information that @Remou gave you in his comments.
To me, there are some unfortunate design choices for this database: getting basic stock data should be as easy as s simple SUM()on the column that hold inventory records.

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

Usually, a simple way to track inventory is to keep track of each stock transaction:

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

• Incoming stock records have a + Quantity
• Outgoing stock records have a - Quantity
• The record should also keep track of the part/item/bar reference (or ID), the date and time of the transaction, and -if you want to manage multiple warehouses- which warehouse ID is involved.

• 进货记录有 + 数量
• 出库库存记录有一个 - 数量
• 该记录还应跟踪零件/项目/棒材参考（或 ID）、交易的日期和时间，以及 - 如果您想管理多个仓库 - 涉及哪个仓库 ID。

So if you need to know the complete stock at hand for all items, all you need to do is something like:

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

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

In your case, while BatchNumber/BarNois your natural key, keeping them in a separate Bartable would have some advantages:

在您的情况下，whileBatchNumber/BarNo是您的自然键，将它们保存在单独的Bar表中会有一些优势：

• You can use Bar.IDto get back the Bar.BatchNumberand Bar.BarNoanywhere you need it.
• You can use BarIDas a foreign key in your BarStock, Salesand Reservationtables. It makes joins easier without having to mess with the complexities of compound keys.

• 您可以使用Bar.ID取回Bar.BatchNumber和Bar.BarNo任何你需要它。
• 您可以BarID在BarStock,Sales和Reservation表中用作外键。它使连接更容易，而不必弄乱复合键的复杂性。

There are things that Access allows that are not really good practice, such as spaces in table names and fields, which end up making things less readable (at least because you need to keep them between []), less consistent with VBA variable names that represent these fields, and incompatible with other database that don't accept anything other than alphanumerical characters for table and field names (should you wish to up-size later or interface your database with other apps).

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

Also, help yourself by sticking to a single naming convention, and keep it consistent:

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

• Do not mix upper and lower case inconsistently: either use CamelCase, or lower case or UPPER case for everything, but always keep to that rule.
• Name tables in the singular -or the plural-, but stay consistent. I prefer to use the singular, like table Partinstead of Parts, but it's just a convention (that has its own reasons).
• Spell correctly: it's Receivednot Recieved. That mistake alone may cost you when debugging why some query or VBA code doesn't work, just because someone made a typo.
• Each table should/must have an IDcolumn. Usually, this will be an auto-increment that guarantees uniqueness of each record in the table. If you keep that convention, then foreign keys become easy to guess and to read and you never have to worry about some business requirement changing the fact that you could suddenly find yourself with 2 identical BatchNumbers, for some reason you can't fathom right now.

• 不要不一致地混合大写和小写：对于所有内容都使用 CamelCase，或小写或大写，但始终遵守该规则。
• 以单数或复数形式命名表格，但要保持一致。我更喜欢使用单数，比如 tablePart而不是Parts，但这只是一种约定（这有其自身的原因）。
• 拼写正确：它Received不是Recieved。在调试为什么某些查询或 VBA 代码不起作用时，仅仅因为有人打错字，这个错误可能会让您付出代价。
• 每个表都应该/必须有一ID列。通常，这将是一个自动增量，以保证表中每条记录的唯一性。如果你保持这个约定，那么外键就变得容易猜测和阅读，你永远不必担心某些业务需求会改变你可能突然发现自己有 2 个相同的事实BatchNumbers，出于某种原因你现在无法理解。

There are lots of debates about database design, but there are certain 'rules' that everyone agrees with, so my recommendation should be to strive for:

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

• Simplicity: make sure that each table records one kind of data, and does not contain redundant data from other tables (normalisation).
• Consistency: naming conventions are important. Whatever you choose, stick to it throughout your project.
• Clarity: make sure that you-in-3-years and other people can easily read the table names and fields and understand what they are without having to read a 300 page specification. It's not always possible to be that clear, but it's something to strive for.

• 简单性：确保每个表记录一种数据，不包含来自其他表的冗余数据（规范化）。
• 一致性：命名约定很重要。无论您选择什么，都要在整个项目中坚持下去。
• 清晰性：确保 3 岁后的您和其他人可以轻松阅读表名和字段并理解它们的含义，而无需阅读 300 页的规范。并不总是那么清楚，但这是值得努力的。