Concatenate and Combine Access Rows

I was working with a dataset in Access, where I had a single field that was different to an otherwise exact data row. I wanted to merge these rows, so I had a unique record; I wanted the single field of different data to be concatenated together, as to not lose the data.

I was able to create a VBA function that I then called from a query to perform the merging of rows. It does have one shortcoming -- if you have duplicated data in the field you are concatenating, it may miss it and add it again. Here's what I mean:

• Removed Duplicates: Data order a, a, a, b, c -- output = a, b, c
  
• Creates Duplicates: Data order a, b, a, c, a -- output = a, b, a, c, a

Note that given the time/effort, you could improve my function to remove all duplicates.
Here's the function. Press Alt-F11 to get to the VBA Editor.

Public Function Concat(email As String) As String
Dim rs As DAO.Recordset
Dim strSQL As String
Concat = ""
strSQL = "SELECT [Some ID] FROM MyTable WHERE [Email Address] = '" & email & "';"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Do While Not rs.EOF

If Concat <> "" Then
' check for duplicate
If Left(Concat, Len(Concat) - 2) <> rs("[Some ID]") Then
Concat = Concat & rs("[Some ID]") & ", "
End If
Else
Concat = Concat & rs("[Some ID]") & ", "
End If

rs.MoveNext
Loop

'remove last ", "
Concat = Left(Concat, Len(Concat) - 2)
Set rs = Nothing
End Function


In your query call the function and pass the lookup variable -- in my case, it's an email address. See "Public Function Concat(email As String) As String" -- Concat is the function name and email is that variable that you are passing. The variable strSQL uses the email address passed to the function. Here's the expression in my query:

• Expr1: Concat([MyTable]![Email Address])

I found this to be a valuable solution that can be re-applied to several situations in working with data in Salesforce.com.