Solved

How to concatenate multiple matching/similar records

Posted on 2009-05-14
9
372 Views
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!
0
Comment
Question by:montarch
  • 5
  • 2
9 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 334 total points
Comment Utility
First, create a query like this:

SELECT
  Company & Location & Department As ID,
  Employees
FROM
  tblEmployees
WHERE
  (Company & Location & Department)=[key];

Save it as qdyEmployeesList

Create another query using the function below:

SELECT DISTINCT
  Company,
  Location,
  Department,
    ConcatenateRecordsTxt("qdyEmployeesList",[Company] & [Location] & [Department],"Employees",", ") AS
  EmployessAll
FROM
  tblEmployees;

Save it as qdyEmployeesAll.
Run this.

/gustav
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

        .MoveNext

      Wend

      .Close

    End With

    

    Set fld = Nothing

    Set rst = Nothing

    Set qdf = Nothing

  End If

  

  Set dbs = Nothing

  

  ConcatenateRecordsTxt = strFields

  

Exit_ConcatenateRecordsTxt:

  Exit Function

  

Err_ConcatenateRecordsTxt:

  MsgBox "Error " & Err.Number & ". " & Err.Description

  Resume Exit_ConcatenateRecordsTxt

  

End Function

Open in new window

0
 

Author Comment

by:montarch
Comment Utility
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-
"WHERE
  (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!

Gregg.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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.

/gustav
0
 

Author Comment

by:montarch
Comment Utility
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!

Gregg
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 3

Assisted Solution

by:DK_User
DK_User earned 166 total points
Comment Utility
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

            Else

                employees = rst2!employees

            End If

            rst2.MoveNext

        Loop

        

        rst2.Close

        sql = "INSERT INTO tblTmp Values('" & rst1!Company & "','" & rst1!Location & "','" & rst1!Department & "','" & employees & "')"

        con.Execute sql

        

    rst1.MoveNext

    

    Loop

Exit Sub

Err_Handler:

    MsgBox "Ups, and unexpected error. Try look at it and write me again" & Chr(10) & Err.Description, vbOKOnly

    

End Sub

Open in new window

Image1.jpg
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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.

/gustav
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 334 total points
Comment Utility
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!

/gustav
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.AddNew

          rstList!Company.Value = !Company.Value

          rstList!Location.Value = !Location.Value

          rstList!Department.Value = !Department.Value

          rstList!Employees.Value = !Employees.Value

      Else

        ' Same Company/Location/Department as previous record.

        ' Append separator and expand list of employees.

        rstList.MoveLast

        rstList.Edit

          rstList!Employees.Value = rstList!Employees.Value & ", " & !Employees.Value

      End If

      ' Save the record.

      rstList.Update

      ' Store the key to compare for the next record.

      strKeyLast = strKey

      .MoveNext

    Wend

    .Close

  End With

  rstList.Close

  

  Set rstList = Nothing

  Set rstRead = Nothing

  Set dbs = Nothing

  

End Sub

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
So did you succeed?

/gustav
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now