背景：
我的公司库存钢筋.我们提议将条形切成碎片.从库存方面来说,我们希望跟踪每个酒吧的长度,从进入仓库的那一刻起,到仓库中的时间(可能会被切成小块),直到整个酒吧出售并消失.
> Barstock(以下字段)
> BatchNumber(收到的所有酒吧,相同的生产热量)
> BarNo(个人酒吧)
> Orginial Length(在库存中收到的条的长度
(BatchNumber和BarNo组合,是主键)
>销售
> ID(主键)
> BatchNumber
> BarNo
>已售出数量
>预订(卖方可以保留一些材料,当客户表示兴趣,但需要时间来决定)
> ID(主键)
> BatchNumber
> BarNo
>数量保留
我想将这三个表中的信息拉到一个列表中,显示：
-Barstock.orginial length收到
– Sales.Quantity已售出售出
– 收到 – 按库存出售
– reservation.Quantity保留为预留
– 现货 – 保留为可用.
问题是我吮吸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
我知道查询多次拉同一条记录,因为;
– 当我删除GROUP BY术语时,我会得到几条完全相同的记录.
– 但是,相应表中只有这些记录的一个实例.
我希望我已经能够正确解释自己,请问我是否需要详细说明.
感谢您抽出宝贵时间来看看我的问题！
解决方法
从您的数据库架构,似乎：
>给定的BatchNumber / BarNo可能有多个销售记录(例如,我可以想象多个客户可能已经购买了相同栏的子部分).
>给定的BatchNumber / BarNo可能有多个Reservation记录(例如,同一个bar的多个部分可能是’reserved’)
要检查这些表中是否确实有多条记录,请尝试以下操作：
SELECT CountOfDuplicates
FROM (SELECT COUNT(*) AS CountOfDuplicates
FROM Sales
GROUP BY BatchNumber & "," & BarNo)
WHERE CountOfDuplicates > 1
如果查询返回一些记录,那么有重复项,这可能是您的查询返回错误值的原因.
从头开始
现在,让你的查询工作的诀窍是真正考虑你想要显示的主要数据是什么,并从那开始：
>您基本上想要股票中所有柱的列表.
这些酒吧中的一些可能已被出售,或者可能被保留,但如果不是,则应显示库存中的数量.您当前的查询永远不会向您显示.
>对于库存中的每个库,您需要列出已售出的数量和预留的数量,并将它们组合在一起以查找剩余可用数量.
所以很清楚,你的中心数据是库存中的柱状列表.
不要试图立即将所有内容都放到一个大型查询中,而是最好为每个目标创建简单查询,并确保在每种情况下都能获得正确的数据.
只是酒吧
根据您的解释,每个单独的栏都记录在BarStock表中.
正如我在评论中所说,根据我的理解,所有交付的酒吧在BarStock表中都有一条记录,没有重复.因此,您应该测量库存的主要清单是BarStock表：
SELECT BatchNumber,BarNo,OrgLength FROM BarStock
只是销售
同样,这应该非常简单：我们只需要找出每个BatchNumber / BarNo对的总销售量：
SELECT BatchNumber,Sum(QtySold) AS SumAvQtySold FROM Sales GROUP BY BatchNumber,BarNo
只是预订
与销售相同：
SELECT BatchNumber,SUM(QtyReserved) AS Reserved FROM Reservation GROUP BY BatchNumber,BarNo
原始库存销售
现在,我们应该能够将前两个查询合并为一个.我不是想优化,只是为了使数据协同工作：
SELECT BarStock.BatchNumber,BarStock.OrgLength,S.SumAvQtySold,(BarStock.OrgLength - Nz(S.SumAvQtySold)) AS OnStock
FROM BarStock
LEFT JOIN (SELECT BatchNumber,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.OrgLength - Nz(S.SumAvQtySold)) AS OnStock
FROM BarStock
LEFT JOIN (SELECT BatchNumber,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,SUM(QtyReserved) AS Reserved
FROM Reservation
GROUP BY BatchNumber,BarNo) AS R
ON (BS.BatchNumber = R.BatchNumber) AND (BS.BarNo = R.BarNo)
注意对连接右侧的项使用Nz()：如果给定的BatchNumber / BarNo对没有Sales或Reservation数据,SumAvQtySold和Reserved的值将为Null并将呈现OnStock和Available无论库存中的实际数量如何,这都不是我们预期的结果.
使用Access中的查询设计器,您必须单独创建3个查询,然后将它们组合在一起.
请注意,查询设计在处理多个LEFT和RIGHT联接方面不是很好,所以我认为你不可能一次性写出整个东西.
一些评论
我相信你应该阅读@Remou在评论中给你的信息.
对我来说,这个数据库有一些不幸的设计选择：获取基本库存数据应该像保存库存记录的列上的简单SUM()一样简单.
通常,跟踪库存的一种简单方法是跟踪每个库存交易：
>进货库存记录有数量
>出货库存记录有 – 数量
>记录还应跟踪零件/项目/条形参考(或ID),交易的日期和时间,以及 – 如果您想管理多个仓库 – 涉及哪个仓库ID.
因此,如果您需要知道所有物品的库存情况,您需要做的就是：
SELECT BarID,Sum(Quantity) FROM StockTransaction GROUP BY BarID
在您的情况下,虽然BatchNumber / BarNo是您的自然键,但将它们保存在单独的Bar表中会有一些优点：
>您可以使用Bar.ID在您需要的任何地方取回Bar.BatchNumber和Bar.BarNo.
>您可以在BarStock,Sales和Reservation表中使用BarID作为外键.它使连接变得更容易,而不必混淆复合键的复杂性.
Access允许的东西并不是很好的做法,例如表名和字段中的空格,最终会使事情变得不那么可读(至少因为你需要将它们保存在[]之间),与VBA变量名不一致代表这些字段,并且与其他数据库不兼容,这些数据库不接受表和字段名称的字母数字字符以外的任何内容(如果您希望稍后调整大小或将数据库与其他应用程序连接).
此外,通过坚持单一命名约定来帮助自己,并保持一致：
>不要不一致地混合使用大写和小写：要么使用CamelCase,要么使用小写或UPPER大小写,但始终遵守该规则.
>以单数形式命名表 – 或复数形式 – 但保持一致.我更喜欢使用单数,如表Part而不是Parts,但它只是一个约定(有其自身的原因).
>拼写正确：收到的不是收到的.在调试为什么某些查询或VBA代码不起作用时,单独这个错误可能会花费你,只是因为某人犯了错字.
>每个表都应该/必须有一个ID列.通常,这将是一个自动增量,保证表中每条记录的唯一性.如果你保持这种惯例,那么外键变得容易猜测和阅读,你永远不必担心一些业务需求改变你可能突然发现自己有2个相同的BatchNumbers的事实,由于某种原因你现在无法理解.
关于数据库设计有很多争论,但是每个人都同意某些“规则”,所以我的建议应该是努力：
>简单性：确保每个表记录一种数据,并且不包含来自其他表的冗余数据(规范化).>一致性：命名约定很重要.无论您选择什么,在整个项目中坚持下去.>清晰度：确保您在3年内和其他人可以轻松阅读表名和字段,并了解它们的内容,而无需阅读300页规范.并不总是那么清楚,但这是值得努力的事情.
