Concatenating One To Many Relationship

[CPGDeveloper]

Apologies if this has been answered before -- I cannot seem to find it here.

I'm trying to convert the following table:

Company, Email
ACME, email1@email.com
ACME, email2@email.com
ACME, email3@email.com
JONES, email@jones.com
JONES, email2@jones.com


into

Company, Email
ACME, email1@email.com;email2@email.com;email3@email.com
JONES, email@jones.com; email2@jones.com


Thanks in advance for your help

 

[My Aswer Is This]

Explain what data is in what cell.

 

[CPGDeveloper]

Actually, I figured it out using this function:

How to concatenate multiple child records into a single value - Microsoft: Access Queries and JET SQL FAQ - Tek-Tips

Thanks!

 

[ranman256]

'This will post to a table at ""Insert into table"

  'run this code from a macro:  RUNCODE  JoinEmails()
Function JoinEmails()
Dim sSql As String, sName As String, sEmail As String
Dim rst 'As Recordset
Dim vLast, vAllEmails
sSql = "select * from TABLE"
Set rst = CurrentDb.OpenRecordset(sSql)
With rst
      sName = .Fields("Name").Value & ""
      
      If vLast <> sName And vLast <> "" Then
            'post emails to a table
        sSql = "Insert into table ([Name],[Emails]) values ('" & sName & "','" & vAllEmails & "')"
        DoCmd.RunSQL sSql
        vAllEmails = ""
      End If
      sEmail = .Fields("Email").Value & ","
      vAllEmails = vAllEmails & sEmail   'store group email
      vLast = sName                      'remember prev name
   .MoveNext
End With
rst.Close
Set rst = Nothing
End Function