Merge partially duplicate rows into one row in Access

Question

I have a select query, it gives out rows that are partially duplicate, I want to the duplicate rows to be merged into one row with the non duplicate values to be comma separated. Current output of the query is:

    **Contacts           Department                    Type**    
    Elena Stone          Biology                       Impact
    Elena Stone          Biochemistry                  Impact
    Elena Stone          Biotechnology                 Other

My desired output should be:

    **Contacts           Department                                 Type**   
     Elena Stone          Biology,Biochemistry,Biotechnology        Impact,other

Can some one please help me how to get this. Thank You

What have you tried already? You should be able to use some VBA to make a new table and concatenate the `Department` and `Type` if the `Contacts` already exist, else make add a new record. I'm sure there is also SQL to do this but I'm not great with SQL. — Grant, Sep 05 '13 at 16:08
Is there a way add this condition in the select query itself, if not how do I go about writing the VBA? I am actually using this query to gather data from tow tables to generate a report. — Ksagar, Sep 05 '13 at 16:15
I'm sorry but I'm not very good with the queries or SQL... But I've added an answer below that works for me. It just doesn't check for duplicates. — Grant, Sep 05 '13 at 19:17
possible duplicate of [Combine rows / concatenate rows](http://stackoverflow.com/questions/92698/combine-rows-concatenate-rows) — Gord Thompson, Sep 05 '13 at 20:37

score 0 · Accepted Answer · answered Sep 05 '13 at 19:16

To get to VBA you press ALT+F11 and that will bring up the editor. Since you're not developing a form, you can just create a module. Go to Insert, and select Module and you'll see a blank window with Option Compare Database across the top.

To start your module you create a sub function and put you're code there:

Public Sub CleverNameHere()

    'Code

End Sub

When you save this, do not name the module the same name as CleverNameHere, because if you ever have to call it then it will look like this ModuleNameHere.CleverNameHere and the parenthesis are for parameters.

From there you will just write something that calls your table, creates a recordset, and takes a record line by line and manipulates it until end of file (EOF). Maybe something like this:

Public Sub CompareDuplicates()

Dim myR As Recordset
Dim myR2 As Recordset

Set myR = CurrentDb.OpenRecordset("Original_Table", dbOpenDynaset)
Set myR2 = CurrentDb.OpenRecordset("Duplicate_Table", dbOpenDynaset)

Do Until myR.EOF = True
    myR2.FindFirst ("Contacts = '" & myR![Contacts] & "'")
    If myR2.NoMatch = True Then
        myR2.AddNew
        myR2![Contacts] = myR![Contacts]
        myR2![Department] = myR![Department]
        myR2![Type] = myR![Type]
        myR2.Update
    Else
        myR2.Edit
        myR2![Department] = myR2![Department] & ", " & myR![Department]
        myR2![Type] = myR2![Type] & ", " & myR![Type]
        myR2.Update
    End If
    myR.MoveNext

Loop

Set myR = Nothing

End Sub

Merge partially duplicate rows into one row in Access

1 Answers1