MS 访问：添加连接相关后"field Could refer to more than one table listed"

Question

如果有类似的OrderNo，我正在尝试Allen-Browne ConcatRelated函数来连接产品名称，但在添加此之后

ConcatRelated("Product.ProductName", "OrderLine", "OrderLine.OrderNo = " & [OrderLine.OrderNo])AS Product 

它显示了类似于此的错误

指定的字段可以引用FROM子句中列出的多个表

但我不知道怎么解决。

这是我的问题：

SELECT OrderLine.OrderNo,  ConcatRelated("Product.ProductName", "OrderLine", "OrderLine.OrderNo = " & [OrderLine.OrderNo])AS Product ,Sum(OrderLine.Quantity) AS SumOfQuantity, Sum([Quantity]*[Price]) AS Expr1
FROM [Order] INNER JOIN (Product INNER JOIN OrderLine ON Product.[ProductNo] = OrderLine.[ProductNo]) ON Order.[OrderNo] = OrderLine.[OrderNo]
GROUP BY OrderLine.OrderNo;

这是我的TransactionQuery在没有函数的情况下的样子：

---------------------------------
OrderNo | TotalItems | Price
1       | 4          | $70
2       | 1          | $25
----------------------------------

这是表格：

Customer Table:
--------------------------
CusomerNo(pk)  | Name
1              | John Wink
2              | John Wake
--------------------------
Order Table:
----------------------------------
OrderNo(pk) | CustomerNo(fk)
1           | 1
2           | 2
----------------------------------
OrderLine Table:
----------------------------------------------------------
OrderNo(fk) | ProductNo(fk) | Quantity | TotalPayment
1           | 1             | 2        | $20  
2           | 2             | 1        | $25 
1           | 2             | 2        | $50
----------------------------------------------------------
Product Table:
-----------------------------------
ProductNo(pk) | ProductName | Price
1             | Burger      | $10
2             | Pizza       | $25
-----------------------------------

编辑：我粘贴了错误的代码，我更改了代码，当前查询给出了一个"编译错误。在查询ConcatRelated("Product.ProductName"、"OrderLine"、"OrderLine.OrderNo="&[OrderLine.OrderNo](AS产品中；

谢谢

Answer 1

ProductName不在OrderLine表中，因此ConcatRelated将失败。保存查询对象-qryOrderProducts:

SELECT OrderLine.OrderNo, Product.ProductName
FROM OrderLine INNER JOIN Product ON OrderLine.ProductNo = Product.ProductNo;

然后这个工作：

SELECT Order.OrderNo, Sum(Quantity) AS SumQty, Sum([Quantity]*[Price]) AS Total, 
ConcatRelated("ProductName","qryOrderProducts","OrderNo = " & [Order].[OrderNo]) AS Products
FROM ([Order] INNER JOIN OrderLine ON Order.OrderNo = OrderLine.OrderNo) 
INNER JOIN Product ON OrderLine.ProductNo = Product.ProductNo
GROUP BY Order.OrderNo;

无法修改ConcatRelated函数，使其允许传递SQL语句并使用该语句生成记录集。其他作者也有类似的功能。