我是訪問和編碼的新手。我有一張包含大量資料的作業表。我希望通過運行查詢或 VBA 在 Access 中指定一些資料。我想要的是將基于列的資料和用逗號分隔的每個資料組合起來。我正在粘貼下面的表格示例：
輸入表
輸出資料 在此處輸入影像描述
我嘗試使用 COMMON 但沒有運氣。請幫忙。
uj5u.com熱心網友回復：
使用 Allen Browne ConcatRelated 函式（經常被參考，從Access開始：ConcatRelated 適用于表，但不適用于查詢）和非規范化資料結構：
SELECT Data.LocationID,
"Paste: " & ConcatRelated("CustomerID","Data","LocationID=" & [LocationID] & " AND NOT Product1 IS NULL")
& "; " & "Chips: " & ConcatRelated("CustomerID","Data","LocationID=" & [LocationID] & " AND NOT Product2 IS NULL")
& "; " & "Oil: " & ConcatRelated("CustomerID","Data","LocationID=" & [LocationID] & " AND NOT Product3 IS NULL")
& "; " & "Pulses: " & ConcatRelated("CustomerID","Data","LocationID=" & [LocationID] & " AND NOT Product4 IS NULL")
& "; " & "Salt: " & ConcatRelated("CustomerID","Data","LocationID=" & [LocationID] & " AND NOT Product5 IS NULL")
& "; " & "Sugar: " & ConcatRelated("CustomerID","Data","LocationID=" & [LocationID] & " AND NOT Product6 IS NULL")
AS PS
FROM Data
GROUP BY Data.LocationID;
資料結構可以使用 UNION 查詢進行規范化。
SELECT LocationID, CustomerID, Product1 AS Product FROM Data WHERE NOT Product1 IS NULL
UNION SELECT LocationID, CustomerID, Product2 FROM Data WHERE NOT Product2 IS NULL
UNION SELECT LocationID, CustomerID, Product3 FROM Data WHERE NOT Product3 IS NULL
UNION SELECT LocationID, CustomerID, Product4 FROM Data WHERE NOT Product4 IS NULL
UNION SELECT LocationID, CustomerID, Product5 FROM Data WHERE NOT Product5 IS NULL
UNION SELECT LocationID, CustomerID, Product6 FROM Data WHERE NOT Product6 IS NULL;
然后生成所需的輸出將涉及另外兩個查詢，每個查詢都呼叫 ConcatRelated 函式。
查詢 1：
SELECT LocationID, Product & ": " &
ConcatRelated("CustomerID","unionData","LocationID=" & [LocationID] & " AND Product='" & [Product] & "'")
AS PS
FROM unionData
GROUP BY LocationID, Product;
查詢 2：
SELECT LocationID,
ConcatRelated("PS","Query1","LocationID=" & [LocationID], "", "; ")
AS ProductSold
FROM Query1
GROUP BY LocationID;
規范化資料的優點是無論涉及多少產品都不必修改最后 2 個查詢，并且查詢中不會經常參考 ConcatRelated 函式。對于非規范化資料，如果添加更多產品列，則必須修改查詢以再次為每個列呼叫函式。可以消除規范化表結構和 UNION 查詢。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/314230.html