MS ACCESS: "field Could refer to more than one table listed" after adding concatrelated

user_18050142 0
Viewed 59

I am trying Allen Browne ConcatRelated function to concatenate product names if there are similar OrderNo, but after adding this

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

It shows an error similar to this

the specified field Could refer to more than one table listed in FROM clause

but i can't figure out how to solve it.

This is my query:

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;

This is my TransactionQuery looks like without the function:

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

This is the tables:

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
-----------------------------------

Edit: I pasted the wrong code, I changed the code and the current query gives a "Compile Error. in query ConcatRelated("Product.ProductName", "OrderLine", "OrderLine.OrderNo = " & [OrderLine.OrderNo])AS Product "

Thank you

ms-access
1 Answers

ProductName is not in OrderLine table so ConcatRelated will fail. Save a query object - qryOrderProducts:

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

Then this works:

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;

Could modify ConcatRelated function so it allows passing an SQL statement and build recordset with that. There are similar functions by other authors that do that.

user_76071900
Related