Why is there zero VBA in posted db, not even your original attempted code? Of course function call won't work because there isn't any such function available.
SQL using Allen's code:
Code:
SELECT DISTINCT tblOrders.ItemID, tblItem.Item, tblOrders.DateOrdered,
ConcatRelated("FullName","qryOrderCustomers","ItemID & DateOrdered ='" & [tblOrders].[ItemID] & [DateOrdered] & "'","FullName","; ") AS Customers
FROM tblItem INNER JOIN tblOrders ON tblItem.ItemID = tblOrders.ItemID;
SQL using theDBGuy version:
Code:
SELECT DISTINCT tblOrders.ItemID, tblItem.Item, tblOrders.DateOrdered,
SimpleCSV("SELECT FullName FROM qryOrderCustomers WHERE ItemID & DateOrdered ='" & [tblOrders].[ItemID] & [DateOrdered] & "' ORDER BY FullName","; ") AS Customers
FROM tblItem INNER JOIN tblOrders ON tblItem.ItemID = tblOrders.ItemID;
or without qryOrderCustomers:
Code:
SELECT DISTINCT tblOrders.ItemID, tblItem.Item, tblOrders.DateOrdered,
SimpleCSV("SELECT FullName FROM tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID WHERE ItemID & DateOrdered ='" & [tblOrders].[ItemID] & [DateOrdered] & "' ORDER BY FullName","; ") AS Customers
FROM tblItem INNER JOIN tblOrders ON tblItem.ItemID = tblOrders.ItemID;
ItemID |
Item |
DateOrdered |
Customers |
1 |
Apple |
12/9/2024 |
Fred |
1 |
Apple |
12/13/2024 |
Fred; Mary |
2 |
Banana |
12/9/2024 |
Fred; John |