Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How to concatenate multiple matching/similar records

Posted on 2009-05-14
Medium Priority
Last Modified: 2012-05-07
I'm trying to consolidate (concatenate) some data. I have the data in an MS Access 2003 database (single table).

Here's an example of the data I have:
Company      Location      Department      Employees
MondoCorp      Houston      Sales      Gene, Ray, Della
MondoCorp      Houston      Sales      Tom, Denise
MondoCorp      Houston      Sales      Ray, Jeffery, Mick
MondoCorp      Houston      IT      Brady, Elsa, Hugo
MondoCorp      Houston      IT      Chuck, Maggie
MondoCorp      Pasadena      Sales      Bucky, Terri
MondoCorp      Pasadena      IT      Uli, Mick, Tami
MondoCorp      Pasadena      IT      Jake, Esra, Remy

I need to take records where the 'Company', 'Location' and 'Department' match, and concatenate the multiple 'Employees' data as such:

Company      Location      Department      Employees
MondoCorp      Houston      Sales      Gene, Ray, Della, Tom, Denise, Ray, Jeffery, Mick
MondoCorp      Houston      IT      Brady, Elsa, Hugo, Chuck, Maggie
MondoCorp      Pasadena      Sales      Bucky, Terri
MondoCorp      Pasadena      IT      Uli, Mick, Tami, Jake, Esra, Remy

Any Ideas?

Thank you so much for your time Experts!
Question by:montarch
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
LVL 52

Accepted Solution

Gustav Brock earned 1336 total points
ID: 24392843
First, create a query like this:

  Company & Location & Department As ID,
  (Company & Location & Department)=[key];

Save it as qdyEmployeesList

Create another query using the function below:

    ConcatenateRecordsTxt("qdyEmployeesList",[Company] & [Location] & [Department],"Employees",", ") AS

Save it as qdyEmployeesAll.
Run this.

Public Function ConcatenateRecordsTxt( _
  ByVal strSource As String, _
  ByVal strKey As String, _
  ByVal strField As String, _
  Optional ByVal strSeparator As String = ";") _
  As String
' Concatenates values from one field (strField) from all
' records in query strSource using parameter Value strKey.
' Values are separated by strSeparator.
' Default output like:
' 34;56;34;67;234
' 1999-10-12. Cactus Data ApS, CPH
  Dim dbs         As DAO.Database
  Dim qdf         As DAO.QueryDef
  Dim rst         As DAO.Recordset
  Dim fld         As DAO.Field
  Dim booPluralis As Boolean
  Dim strFields   As String
  On Error GoTo Err_ConcatenateRecordsTxt
  Set dbs = CurrentDb()
  If Len(strSource) > 0 And Len(strField) > 0 Then
    Set qdf = dbs.QueryDefs(strSource)
    qdf.Parameters(0) = strKey
    Set rst = qdf.OpenRecordset()
    Set fld = rst.Fields(strField)
    With rst
      While Not .EOF
        If booPluralis = True Then
          ' There is more than one record.
          ' Add separator.
          strFields = strFields & strSeparator
        End If
        strFields = strFields & Trim(fld.value)
        booPluralis = True
    End With
    Set fld = Nothing
    Set rst = Nothing
    Set qdf = Nothing
  End If
  Set dbs = Nothing
  ConcatenateRecordsTxt = strFields
  Exit Function
  MsgBox "Error " & Err.Number & ". " & Err.Description
  Resume Exit_ConcatenateRecordsTxt
End Function

Open in new window


Author Comment

ID: 24395464
Thanks cactus data (Gustav?)- this looks very helpful- I appreciate your time.

A couple of stupid questions (the stupid ones generally being the easiest to answer-except for me:>)
In the first sql you show-
  (Company & Location & Department)=[key];"
- what would I be using for '[key]'

In the code you provided, do I put that into a vba module? If so, how does it execute- so far this database is pretty sparse- no forms, etc.

Thanks again for your quick response!

LVL 52

Expert Comment

by:Gustav Brock
ID: 24395609
The key could be named anything, it's a parameter. The code just picks the first (and only) parameter:

   qdf.Parameters(0) = strKey

Yes, copy and paste the function into an empty module.
Check under menu Tools, References that a reference is set for:

  Microsoft DAO V3.60

or similar.
The function is called by the query - nothing to execute manually - just run the second query.

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


Author Comment

ID: 24400742
Thanks again Gustav,

1) In your code example, am I assuming correctly that I need to customize your examples to fit my application? That is, 'strSource' to 'myquerysname', 'strField' to 'myfieldsname', etc..?

2) Does the information you provided apply strictly to the limited example I provided (with 1 company), or would it also fit for this extended example:

Company      Branch      Department      Employee(s)
MondoCorp      Houston      Sales      Gene, Ray, Della
MondoCorp      Houston      Sales      Tom, Denise
MondoCorp      Houston      Sales      Ray, Jeffery, Mick
MondoCorp      Houston      IT      Brady, Elsa, Hugo
MondoCorp      Houston      IT      Chuck, Maggie
MondoCorp      Pasadena      Sales      Bucky, Terri
MondoCorp      Pasadena      IT      Uli, Mick, Tami
MondoCorp      Pasadena      IT      Jake, Esra, Remy
IttybittyCO.      Denver      service      Biffy, Buffy, Bobby
IttybittyCO.      Denver      service      Shelly, Kelly, Telly
IttybittyCO.      Denver      service      Ricky, Micky
IttybittyCO.      Denver      finance      Zeke, Deke
IttybittyCO.      Miami      finance      Will, Mark
GeneralLTD      Chicago      IT      Paul, Saul, Raul
GeneralLTD      Chicago      IT      Kenny
GeneralLTD      Chicago      IT      Penny, Lemmy
GeneralLTD      Seattle      sales      Joe

Thanks again for your time, Gustav!


Assisted Solution

DK_User earned 664 total points
ID: 24401167
Not wanting to interfer with you communications, but i could suggest another solution.

You could start of by moving your data to a temporary table, and in the same step do the consolidation in that table. That way you get a new table with the correct data, and from that you can work on.

My exampel requires:
Make a new table, give it the name tblTmp
Put in 4 fields, all of type Text.
Company, Location, Department,Employees - in that order in the table.
Make a new Form, with 1 button, and on the buttons Click Event, copy the code from the exampel.
Try a Debug after the paste. You might need to reference MS Active Dataobjects in VBA. See image attached. (Perhaps you dont have version2.8, but another version is ok)
BEWARE - You will have to put the correct name of your original table in the code since i didnt see you mention it.
Try it out. It might work at the first attempt, else just write again.

On Error GoTo Err_Handler
Dim con As ADODB.Connection
Dim rst1 As New ADODB.Recordset, rst2 As New ADODB.Recordset, rst3 As ADODB.Recordset
Dim sql As String, employees As String
'You need to apply your own tablename here
Const tableName = "tblbase"
Set con = CurrentProject.Connection
sql = "SELECT Company, Location, Department"
sql = sql & " FROM " & tableName
sql = sql & " GROUP BY Company, Location, Department;"
rst1.Open sql, con, adOpenDynamic, adLockReadOnly
    Do Until rst1.EOF
        sql = "SELECT Employees FROM " & tableName
        sql = sql & " WHERE Company ='" & rst1!Company & "'"
        sql = sql & " AND Location ='" & rst1!Location & "'"
        sql = sql & " AND Department ='" & rst1!Department & "'"
        rst2.Open sql, con, adOpenDynamic, adLockReadOnly
        employees = ""
        Do Until rst2.EOF
            If Len(employees) > 0 Then
                employees = employees & "," & rst2!employees
                employees = rst2!employees
            End If
        sql = "INSERT INTO tblTmp Values('" & rst1!Company & "','" & rst1!Location & "','" & rst1!Department & "','" & employees & "')"
        con.Execute sql
Exit Sub
    MsgBox "Ups, and unexpected error. Try look at it and write me again" & Chr(10) & Err.Description, vbOKOnly
End Sub

Open in new window

LVL 52

Expert Comment

by:Gustav Brock
ID: 24401776
1. No. The only thing that may need to be changed is the name of your table where I have used tblEmployees - except that you now have changed Location to Branch. If so, this must be changed in the code above.

2. It works for any combination of Company, Location/Branch, Department

But why not just get going and test? It works here.

LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 1336 total points
ID: 24414754
DK, the idea of using a temp table is very good but it can be done somewhat simpler - in a way that will run _much_ faster than any other method. The reason is that the source table is read once only, and the target table is opened once only and DAO is way faster to add records than individual SQL "Insert Into .." commands.

The trick is this:

  ' Select an ordered list of Company, Location, Department.

First, create a table (tblEmployeesList) with identical field structure as the master (here called tblEmployees).
If the list of employees can be very long, change the field type for Employees from Text to Memo.

Then run the code below, and the requested list will be filled into tblEmplyeesList.
Gregg, finally use tblEmployeesList as your source for a report or whatever.

Have fun!

Public Sub EmployeesConcatenate()
  Dim dbs         As DAO.Database
  Dim rstRead     As DAO.Recordset
  Dim rstList     As DAO.Recordset
  Dim strKey      As String
  Dim strKeyLast  As String
  Set dbs = CurrentDb
  ' Clean tblEmployeesList.
  dbs.Execute "Delete * From tblEmployeesList"
  ' Select an ordered list of Company, Location, Department.
  Set rstRead = dbs.OpenRecordset("Select * From tblEmployees Order By Company, Location, Department")
  Set rstList = dbs.OpenRecordset("Select * From tblEmployeesList")
  With rstRead
    ' Loop through the records.
    While .EOF = False
      strKey = !Company.Value & !Location.Value & !Department.Value
      If strKey <> strKeyLast Then
        ' New Company/Location/Department.
        ' Create new record.
          rstList!Company.Value = !Company.Value
          rstList!Location.Value = !Location.Value
          rstList!Department.Value = !Department.Value
          rstList!Employees.Value = !Employees.Value
        ' Same Company/Location/Department as previous record.
        ' Append separator and expand list of employees.
          rstList!Employees.Value = rstList!Employees.Value & ", " & !Employees.Value
      End If
      ' Save the record.
      ' Store the key to compare for the next record.
      strKeyLast = strKey
  End With
  Set rstList = Nothing
  Set rstRead = Nothing
  Set dbs = Nothing
End Sub

Open in new window

LVL 52

Expert Comment

by:Gustav Brock
ID: 24439606
So did you succeed?


Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question