对Microsoft访问中外键链接的值求和

Question

我正在努力创建一个查询或其他一些替代方法，从外键相等的表的总和中计算出平衡。

以下是两个表结构：

产品线

ProductLineID      | AutoNumber
CompanyName        | Short Text
OrderID            | Number
ReferenceNumber    | Short Text
ProductDescription | Short Text
Quantity           | Number
PricePerUnit       | Currency
LineTotal          | Calculated
DueDate            | Date/Time

交货线

DeliveryLineID   | AutoNumber
DeliveryID       | Number
ProductLineID    | Number
DeliveryQuantity | Number

一条产品线可以有多个交货线，因此交货线中的外键

这是我到目前为止所拥有的：

SELECT [DeliveryLines].[ProductLineID], 
Sum([DeliveryLines]![DeliveryQuantity]) AS QuantityDelivered
FROM [DeliveryLines] GROUP BY [DeliveryLines].[ProductLineID];

这正确地计算出每个产品线的总和。问题是我需要能够将其链接到[产品线]。[ProductLineID] 改为，以便用户可以检索所有产品系列信息，并让它检索每个产品系列的余额(以及所有其他列(。当产品线没有任何交付线时，我还需要它有一个例外，最好显示 [产品线]。[数量]代替。

任何帮助将不胜感激，

安 迪

Answer 1

你需要的是一个简单的JOIN

SELECT ProductLines.ProductLineID, CompanyName, OrderID, [any other fields],
SUM(Nz(DeliveryQuantity,0)) AS QuantityDelivered
FROM ProductLines
LEFT JOIN DeliveryLines ON ProductLines.ProductLineID=DeliveryLines.ProductLineID
GROUP BY ProductLines.ProductLineID, CompanyName, OrderID, [any other fields]

仅当名称包含空格或特殊字符时，才需要括号，并且当查询中的多个表具有同名的字段时，才需要字段的表名称。

您可以使用如下FIRST来避免GROUP BY每个字段ProductLines：

SELECT ProductLines.ProductLineID, FIRST(CompanyName) AS FirstCompanyName
…
GROUP BY ProductLines.ProductLineID

Answer 2

我的解决方案，您的起始查询作为子查询，如果为 Null，则替换QuantityDelivered。

SELECT
[ProductLines].[ProductLineID]
, [ProductLines].[CompanyName]
, [ProductLines].[OrderID]
, [ProductLines].[ReferenceNumber]
, [ProductLines].[ProductDescription]
, [ProductLines].[PricePerUnit]
, [ProductLines].[LineTotal]
, [ProductLines].[DueDate]
, Nz(t.QuantityDelivered,ProductLines.Quantity) AS Quantity
FROM
ProductLines
LEFT JOIN
(
SELECT
[DeliveryLines].[ProductLineID]
, SUM([DeliveryLines].[DeliveryQuantity]) AS QuantityDelivered
FROM
[DeliveryLines]
GROUP BY
[DeliveryLines].[ProductLineID]
)
AS t
ON
ProductLines.ProductLineID=t.ProductLineID

Answer 3

找到了解决方案：

SELECT
[Product Lines].[Product Line ID]
, [Product Lines].[Company Name]
, [Product Lines].[Order ID]
, [Product Lines].[Reference Number]
, [Product Lines].[Product Description]
, [Product Lines].[Quantity]
, [Product Lines].[Price Per Unit]
, [Product Lines].[Line Total]
, [Product Lines].[Due Date]
, SUM(Nz([Delivery Quantity],0)) AS QuantityDelivered
FROM
[Product Lines]
LEFT JOIN
[Delivery Lines]
ON
[Product Lines].[Product Line ID] = [Delivery Lines].[Product Line ID]
GROUP BY
[Product Lines].[Product Line ID]
, [Product Lines].[Company Name]
, [Product Lines].[Order ID]
, [Product Lines].[Reference Number]
, [Product Lines].[Product Description]
, [Product Lines].[Quantity]
, [Product Lines].[Price Per Unit]
, [Product Lines].[Line Total]
, [Product Lines].[Due Date]
;

它很乱，冗长，但它有效。