Combine values from related rows into a single cell

I am trying to pull data from a access database.  I then want to take this data and send it out in a mail merged email.  I am using a table that has the following:

Name                  address                           class
Bugs bunny           Rabbit
Bugs bunny           carrot eater
Bugs bunny            Hole digger
Bugs bunny           Varmit
Daffy Duck             Duck
Daffy Duck             Space Duck

what I'd like it sto be able to combine all of the class flield into one so that each character has only one line.  Like this:

Name                  address                           class
Bugs bunny           Rabbit, carrot eater, Hole digger, Varmit
Daffy Duck             Duck, Space Duck

This would allow me to send only 1 email per name, not the 4 for bugs bunny and 2 for daffy duck.  (all the names and email address have been changed....)

Appricate any help I can get.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
take a look at the function defined in this refernece:
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may give this a try...
Assuming the data sheet is called "Sheet1". The code will insert a new sheet called "Combined Data" with the desired output.
Sub CombineData()
Dim sws As Worksheet, dws As Worksheet
Dim x, y(), dict, it
Dim i As Long, j As Long

Set sws = Sheets("Sheet1")  'Source Data Sheet

On Error Resume Next
Set dws = Sheets("Combined Data")   'Destination Sheet
On Error GoTo 0

If dws Is Nothing Then
    Set dws = Sheets.Add(after:=sws)
    dws.Name = "Combined Data"
End If

x = sws.Range("A1").CurrentRegion.Value
Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(x, 1)
    If Not dict.exists(x(i, 1) & "_" & x(i, 2)) Then
        dict.Item(x(i, 1) & "_" & x(i, 2)) = x(i, 3)
        dict.Item(x(i, 1) & "_" & x(i, 2)) = dict.Item(x(i, 1) & "_" & x(i, 2)) & ", " & x(i, 3)
    End If
Next i
ReDim y(1 To dict.Count, 1 To 2)
For Each it In dict.keys
    j = j + 1
    y(j, 1) = Split(it, "_")(0)
    y(j, 2) = Split(it, "_")(1)
Next it
dws.Range("A1").Resize(j, 2).Value = y
dws.Range("C1").Resize(dict.Count, 1).Value = Application.Transpose(dict.items)
Set dict = Nothing
End Sub

Open in new window

If you are using a mail merge, you have no option but what Dale suggested.  However, if you are using OLE automation, the possibilities are expanded.  Here are three samples.  The first is a letter with a simple list, the second is a sample with all the programmed tables and the third is a sample with one of the tables.BookmarkSample1.JPGBookmarkSample2.JPGBookmarkSample3.JPG
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

CeredAuthor Commented:
I'm adfraid i am much of an access guy - where do i run the code from?
What Dale posted is a function so you would add it in your query.
Dale FyeOwner, Developing Solutions LLCCommented:
The URL I posted provides an example of how to write the SQL string and the syntax for calling the function properly.
CeredAuthor Commented:
not sure where i  "would add it to my query"
Dale FyeOwner, Developing Solutions LLCCommented:
You will have to copy the function from below and paste it in a code module.  To do this,

1.  open your database, then press Ctrl-G to open the Visual Basic Editor.
2.  Right click on Modules, select insert module
3.  Type a new name for the module (I used mod_Concatenate) in the properties window
4.  Place your cursor in the module on the right side of the screen and paste the code below into the window
5.  Click the save button on your menu bar.

Then to get the results you want, create a query that looks like:

SELECT Name, Address, fconcatfld("YourTableName", "Name", "Class", "string", [Name]) as Class
FROM yourTableName
Group by Name

Replace "YourtTableName" in the function and in the FROM clause of the query with the name of the table, then run the query.

Function fConcatFld(stTable As String, _
                    stForFld As String, _
                    stFldToConcat As String, _
                    stForFldType As String, _
                    vForFldVal As Variant) _
                    As String
'Returns mutiple field values for each unique value
'of another field in a single table
'in a semi-colon separated format.
'Usage Examples:
'   ?fConcatFld(("Customers","ContactTitle","CustomerID", _
'                "string","Owner")
'Where  Customers     = The parent Table
'       ContactTitle  = The field whose values to use for lookups
'       CustomerID    = Field name to concatenate
'       string        = DataType of ContactTitle field
'       Owner         = Value on which to return concatenated CustomerID
Dim lodb As Database, lors As Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"

    On Error GoTo Err_fConcatFld
    lovConcat = Null
    Set lodb = CurrentDb
    loSQL = "SELECT [" & stFldToConcat & "] FROM ["
    loSQL = loSQL & stTable & "] WHERE "
    Select Case stForFldType
        Case "String":
            loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
        Case "Long", "Integer", "Double":    'AutoNumber is Type Long
            loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
        Case Else
            GoTo Err_fConcatFld
    End Select
    Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
    'Are we sure that duplicates exist in stFldToConcat
    With lors
        If .RecordCount <> 0 Then
            'start concatenating records
            Do While Not .EOF
                lovConcat = lovConcat & lors(stFldToConcat) & "; "
            GoTo Exit_fConcatFld
        End If
    End With
    'That's it... you should have a concatenated string now
    'Just Trim the trailing ;
    fConcatFld = Left(lovConcat, Len(lovConcat) - 2)

    Set lors = Nothing: Set lodb = Nothing
    Exit Function

    MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
    Resume Exit_fConcatFld
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
My apologies. I didn't look at the Access tag and assumed that your data is in an Excel File, that's why I offered that solution which is clearly irrelevant. :(
CeredAuthor Commented:
no problem Subodh - it was in excel and then i moved it into access.  appricate the help!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Cered! Glad we could help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.