Combine data from multiple rows to a single record - part 2

[oh704]

Hi All,

I have a similar problem to GreenLil with my data.
GreenLil, please excuse me for copying your sample data .

Label ID First Name Last Name
10C000136 Bob Smith
10C000136 Jane Smith
10C000136 John Smith
10C000136 Bob Smith

I need to combine the unique first, last names into one record and achieve the following desired result.

Label ID Name
10C000136 Bob Smith & Jane Smith & John Smith

i don't believe this can be done on a sql, but perhaps something on vba and load the unique name into an array?

please advise.

Thanks so much.

 

[MajP]

Public Function concatRecords(lblID As Variant) As String
  Dim rs As DAO.Recordset
  Dim strSql As String
  
  strSql = "select * from tblData where [label ID] = '" & lblID & "'"
  Set rs = CurrentDb.OpenRecordset(strSql)
  
  Do While Not rs.EOF
    concatRecords = concatRecords & rs![First Name] & " " & rs![last Name] & "; "
    rs.MoveNext
  Loop
  If Not concatRecords = "" Then
    concatRecords = Left(concatRecords, Len(concatRecords) - 2)
  End If
End Function

SELECT DISTINCT 
 tblData.[Label ID], 
 concatRecords([label id]) AS [Names]
FROM 
 tblData;

There are some other FAQS as well

 

[dhookom]

Check faq701-4233.

Duane
Hook'D on Access
MS Access MVP