MS Access Concatenate function -- allow no duplicates

Question

looking through StackOverflow I was able to find what I was looking for from:
Concatenating multiple rows into single line in MS Access [duplicate]
Which utilizes the function from:
Concatenate values from related records
But what I can't figure out is how to not return duplicate values from the rows that are being concatenated. Examples...I want it to return for Customer1:

PartA, PartB, PartF

Not...

PartA, PartA, PartA, PartB, PartB, PartF

I am not honestly sure on how to make this custom function work like that. My query is...

SELECT DISTINCT PART_NUMBER, 
ConcatRelated("SUPPLIER_NAME","tbl_supplier_list",
              "PART_NUMBER=""" & [PART_NUMBER] & """")
FROM tbl_supplier_list;`

Within the arguments, number 3, it's stated "If the foreign key field is Text (not Number), include quote marks as delimiters, e.g.: "[ForeignKeyFieldName] = """ & [PrimaryKeyFieldName] & """"" So I used this as the keys are not just numbers but include text. — user3417575, Nov 24 '15 at 22:35

Smandoli · Answer 1 · 2015-11-25T05:21:50.100

Modify the ConcatRelated() function from Allen Browne as follows:

Do While Not rs.EOF
    If bIsMultiValue Then
        'For multi-valued field, loop through the values
        Set rsMV = rs(0).Value
        Do While Not rsMV.EOF
            If Not IsNull(rsMV(0)) Then
              If InStr(strOut, rsMV(0)) = 0 then  ''''' ADD THIS
                strOut = strOut & rsMV(0) & strSeparator
              End If                         ''''' ADD THIS
            End If
            rsMV.MoveNext
        Loop
        Set rsMV = Nothing
    ElseIf Not IsNull(rs(0)) Then
        strOut = strOut & rs(0) & strSeparator
    End If
    rs.MoveNext
Loop

The InStr() function will check to see if the current value is already in the output string strOut. If it is (as indicated by a return of zero), the If() statement will ensure the current value is not added.

MS Access Concatenate function -- allow no duplicates

1 Answers1