Solved

VBA Concatinate feild values grouped by another field

Posted on 2006-11-15
18
593 Views
Last Modified: 2012-08-14
Hi All,

I've got ProjectCode and CustomerName fields with a one to many relationship of any number... could be 1 to 2 or 1 to 20!

I need to concatinate the CustomerName field with commers in between and i don't think it can be done with SQL but perhaps with a loop statement in VBA?

So This:

ProjectCode    CustomerName
  A1                 Name1
  A1                 Name2
  A1                 Name3

Would become:

ProjectCode    CustomerName
  A1                 Name1, Name2, Name3

Hope someone can help.

Cheers,
Brett
0
Comment
Question by:brettanderson77
  • 9
  • 5
  • 4
18 Comments
 
LVL 58

Accepted Solution

by:
harfang earned 480 total points
ID: 17951994
Hello brettanderson77

That would be:

    DConcat("CustomerName", "tblYourTableAbove", "ProjectCode = 'A1'")

And the DConcat function is:


Function DConcat(Expr As String, Domain As String, _
    Optional Criteria = Null, _
    Optional Delim As String = ", ")
'
' Similar to DLookup(), but returning all values found
'
    DConcat = Null
    With New ADODB.Recordset
        .Open "Select " & Expr & " From " & Domain & " Where " + Criteria _
        , CurrentProject.Connection, adOpenStatic, adLockReadOnly
        If Not .EOF Then
            DConcat = .GetString(RowDelimeter:=Delim)
            DConcat = Left(DConcat, Len(DConcat) - Len(Delim))
        End If
    End With
   
End Function


Cheers!
(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 17952015
And used in a query:

SELECT
   ProjectCode,
   DConcat("CustomerName", "tblYourTableAbove", "ProjectCode = '" & ProjectCode & "'")
FROM tblProjects

(°v°)
0
 

Author Comment

by:brettanderson77
ID: 17952020
Hi harfang,

Thanks for the quick response.

A1 is just an example of one ProjectCode and i need to do it for many.

What change would I need to make to accomdate this?

Cheers,
Brett
0
 

Author Comment

by:brettanderson77
ID: 17952411
Here's a piece of Java code using recordsets that does a similar thing.... i'm not sure quite how to convert the logic into VBA syntax though:

String endResult = "";
String customerNames = "";
 
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@<servername>:1521:<SID>", "<username>", "<password>");
Statement stmt = conn.createStatement();    
ResultSet rs = stmt.executeQuery("select CODE from code");
while (rs.hasNext()) {
 String code = (String) rs.next();
 ResultSet rs2 = stmt.executeQuery("select CustomerName from Customer");
 while (rs2.hasNext()) {
  String customerName = (String) rs2.next();
  customerNames = customerNames + customerName + ",";
 }
 endResult = endResult + "CODE: " + code + ", Customer Names: " + customerNames + ".";
}

so the end result will be like this:
"CODE: 123, Customer Names: abc, def, ghi, jkl, CODE:456, CustomerNames: mno,pqr,"
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 20 total points
ID: 17952630
try this
place this codes in a module

Function ConcatenateThem(X As String) As String
Dim rs As DAO.Recordset, strTemp As String
Set rs = CurrentDb.OpenRecordset("select * From XYZTable Where ProjectCode='" & X & "' order by CustomerName")

    rs.MoveFirst
 
    Do Until rs.EOF
      strTemp = strTemp & rs("CustomerName") & ","
      rs.MoveNext
    Loop

    ConcatenateThem = Left(strTemp, Len(strTemp) - 1)
End Function


to use in a query, just replace ZYXTable with the actual tablename

SELECT XYZTable.ProjectCode, ConcatenateThem([ProjectCode]) AS Expr1
FROM XYZTable
GROUP BY XYZTable.ProjectCode, ConcatenateThem([ProjectCode]);
0
 

Author Comment

by:brettanderson77
ID: 17952724
Thanks,

I've changed them to reflect correct names etc and i recieve the following error when running the query:

Compile error . in query expression 'ConcatenateCustomer([EOE Project Code])'.

And there is no "." there?


===============
Function ConcatenateCustomer(X As String) As String
Dim rs As DAO.Recordset, strTemp As String
Set rs = CurrentDb.OpenRecordset("select * From Query 30_00_00: Proj DIR Select Where EOE Project Code='" & X & "' order by Customer Name")

    rs.MoveFirst
 
    Do Until rs.EOF
      strTemp = strTemp & rs("Customer Name") & ","
      rs.MoveNext
    Loop

    ConcatenateCustomer = Left(strTemp, Len(strTemp) - 1)
End Function
==================
SELECT [EOE Project Code], ConcatenateCustomer([EOE Project Code]) AS ConCustomer
FROM [Query 30_00_01: Proj DIR NAT Total]
GROUP BY [EOE Project Code], ConcatenateCustomer([EOE Project Code]);
0
 

Author Comment

by:brettanderson77
ID: 17952737
Also should the line in the code be bracketed like so? [ ]

Set rs = CurrentDb.OpenRecordset("select * From [Query 30_00_00: Proj DIR Select] Where [EOE Project Code]='" & X & "' order by [Customer Name]")
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17952742

change this

Set rs = CurrentDb.OpenRecordset("select * From Query 30_00_00: Proj DIR Select Where EOE Project Code='" & X & "' order by Customer Name")


to


Set rs = CurrentDb.OpenRecordset("select * From [Query 30_00_01: Proj DIR NAT Total] Where [EOE Project Code]='" & X & "' order by [Customer Name]")

0
 
LVL 58

Expert Comment

by:harfang
ID: 17952862
brettanderson77,

Are you still pursuing my very simple query solution {http:#17952015} or are you going with capricorn's code? It's basically the same, but using a loop (slower) instead of the GetString method...

Just wondering

(°v°)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:brettanderson77
ID: 17953073
I'm happy to use either, obviously quicker would be better and currently capricorn's isn't working.

It appeared as though your solution didn't cater for other project code values?

Cheers,
Brett
0
 
LVL 58

Expert Comment

by:harfang
ID: 17953091
Of course it does. Again, look at the query.
(°v°)
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17953103
<currently capricorn's isn't working.>

if you will follow what i posted it will work.

you are using  different tables in the function and the query.

you should use the same table.


0
 

Author Comment

by:brettanderson77
ID: 17953108
Actually sorry i missread your code.

I've tried the SQL statement with and without [ ] and the folliowing error occurs:

"Compile error in etc etc"

==============
SELECT[EOE Project Code], DConcat("[Customer Name]", "[Query 30_00_01: Proj DIR NAT Tota]l", "[EOE Project Code] = '" & [EOE Project Code] & "'")
FROM [Query 30_00_01: Proj DIR NAT Total];
0
 

Author Comment

by:brettanderson77
ID: 17953119
Hi capricorn,

Yes i've changed everything and its not happening:

Function ConcatenateCustomer(X As String) As String
Dim rs As DAO.Recordset, strTemp As String
Set rs = CurrentDb.OpenRecordset("select * From [Query 30_00_01: Proj DIR NAT Total] Where [EOE Project Code]='" & X & "' order by [Customer Name]")

    rs.MoveFirst
 
    Do Until rs.EOF
      strTemp = strTemp & rs("Customer Name") & ","
      rs.MoveNext
    Loop

    ConcatenateCustomer = Left(strTemp, Len(strTemp) - 1)
End Function
================
SELECT [EOE Project Code], ConcatenateCustomer([EOE Project Code]) AS ConCustomer
FROM [Query 30_00_01: Proj DIR NAT Total]
GROUP BY [EOE Project Code], ConcatenateCustomer([EOE Project Code]);
0
 

Author Comment

by:brettanderson77
ID: 17953167
Hi Harfang,

My mistake, i misplaced a bracket (always the way!)

It works but is a little slow and i'll have to use DISTINCT as it returns each record as many times as there is a customer.

Thanks!!!

Are you sure this solution would be quicker?

Cheers,
Brett
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 17953226
just tried it and gives me

EOE Project Code      ConCustomer
A1                       Name1,Name2,Name3
0
 
LVL 58

Expert Comment

by:harfang
ID: 17955191
Hello brettanderson77

Well, you can have it faster, but that would require some much more complex coding, and would have to be based on the raw tables entirely.

This being said, the use of DISTINCT is horrible in this context, and creates the bulk of the slow-down. If you require DISTINCT, I'm guessing you base your query on the same table or query that is used in the function. Don't you have a table of which [EOE Project Code] is the key?

My suggestion is always to build your queries on the tables. I suspect that [Query 30_00_01: Proj DIR NAT Total] isn't just a simple select query, either. Show us your tables with some sample data!

Cheers!
(°v°)
0
 

Author Comment

by:brettanderson77
ID: 17961178
Hi Guys, I have shifted around some of the other queries and made a table and am happy with the result.

Thank you both for your help, much appreciated.

Cheers,
Brett
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

13 Experts available now in Live!

Get 1:1 Help Now