ConcatRelated Function Returns All Values

Question

Using Allen Browne's ConcatRelated function is not returning correctly.

Example:

OrderNumber Product Types
00054001021 ROUND
00054001021 WHITE
00054001121 CONCAVE
00054001121 SCORED
00054001121 WHITE
00054001221 CAPSULE
00054001221 SCORED
00054001221 WHITE

Using this:

SELECT DISTINCT YourTable2.OrderNumber, ConcatRelated("[Product Types]","YourTable2","[OrderNumber]="& "[OrderNumber]","[Product Types]",",") AS All_Product_Types
FROM YourTable2;

Is returning all of the ProductTypes for each Order Number:

OrderNumber All_Product_Types
00054001021 CAPSULE,CONCAVE,ROUND,SCORED,SCORED,WHITE,WHITE,WHITE
00054001121 CAPSULE,CONCAVE,ROUND,SCORED,SCORED,WHITE,WHITE,WHITE
00054001221 CAPSULE,CONCAVE,ROUND,SCORED,SCORED,WHITE,WHITE,WHITE

It should be:

OrderNumber     All_Product_Types
00054001021     ROUND, WHITE
00054001121     CONCAVE, SCORED, WHITE
00054001221     CAPSULE, SCORED, WHITE

What am I doing wrong?

score 0 · Answer 1 · answered Mar 28 '17 at 12:57

I actually solved this myself. I needed more quotation marks (") since my Order Number field is text. In case someone else has the same issue, the query should look like this: SELECT DISTINCT YourTable2.OrderNumber, ConcatRelated("[Product Types]","YourTable2","[OrderNumber]="""& [OrderNumber] & """","[Product Types]",",") AS All_Product_Types FROM YourTable2;

ConcatRelated Function Returns All Values

1 Answers1