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        BBunny@me.com           Rabbit
Bugs bunny        BBunny@me.com           carrot eater
Bugs bunny        BBunny@me.com            Hole digger
Bugs bunny        BBunny@me.com           Varmit
Daffy Duck         DDuck@me.com             Duck
Daffy Duck         DDuck@me.com             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        BBunny@me.com           Rabbit, carrot eater, Hole digger, Varmit
Daffy Duck         DDuck@me.com             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.
CeredAsked:
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 FyeCommented:
take a look at the function defined in this refernece:

http://theaccessweb.com/modules/mdl0008.htm
0
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
dws.Cells.Clear
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)
    Else
        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)
dws.UsedRange.Columns.AutoFit
Set dict = Nothing
End Sub

Open in new window

0
PatHartmanCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

CeredAuthor Commented:
I'm adfraid i am much of an access guy - where do i run the code from?
0
PatHartmanCommented:
What Dale posted is a function so you would add it in your query.
0
Dale FyeCommented:
The URL I posted provides an example of how to write the SQL string and the syntax for calling the function properly.
0
CeredAuthor Commented:
not sure where i  "would add it to my query"
0
Dale FyeCommented:
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) & "; "
                .MoveNext
            Loop
        Else
            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)


Exit_fConcatFld:
    Set lors = Nothing: Set lodb = Nothing
    Exit Function

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

Open in new window

0

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:
@Cered
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. :(
0
CeredAuthor Commented:
no problem Subodh - it was in excel and then i moved it into access.  appricate the help!
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Cered! Glad we could help.
0
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
Databases

From novice to tech pro — start learning today.