Combine cells in access separated by comma-CodePudding

I am new to access and coding. I have a sheet comprising of huge data. I want some specified data in Access by running a query or VBA. What I want is to combine the data based on a column and each data separated by a comma. I am pasting the example of the sheet below:

Input sheet

Output data enter image description here

I tried to use COMMON but with no luck. Please help.

CodePudding user response：

Using Allen Browne ConcatRelated function (often referenced, start with Access: ConcatRelated works on a table, but not on a query) with non-normalized data structure:

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;

Data structure can be normalized with UNION query.

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;

Then producing desired output will involve two more queries, each calling ConcatRelated function.

Query1:

SELECT LocationID, Product & ": " & 
ConcatRelated("CustomerID","unionData","LocationID=" & [LocationID] & " AND Product='" & [Product] & "'")
AS PS
FROM unionData
GROUP BY LocationID, Product;

Query2:

SELECT LocationID, 
ConcatRelated("PS","Query1","LocationID=" & [LocationID], "", "; ")
AS ProductSold
FROM Query1
GROUP BY LocationID;

Advantages of normalized data is the last 2 queries don't have to be modified regardless of how many products are involved and ConcatRelated function is not referenced in query as often. With non-normalized data, if you add more product columns, query will have to be modified to call function again for each. Normalize table structure and UNION query can be eliminated.