Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

VBA Concatinate feild values grouped by another field

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
brettanderson77
Asked:
brettanderson77
  • 9
  • 5
  • 4
2 Solutions
 
harfangCommented:
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
 
harfangCommented:
And used in a query:

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

(°v°)
0
 
brettanderson77Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
brettanderson77Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
brettanderson77Author Commented:
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
 
brettanderson77Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:

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
 
harfangCommented:
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
 
brettanderson77Author Commented:
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
 
harfangCommented:
Of course it does. Again, look at the query.
(°v°)
0
 
Rey Obrero (Capricorn1)Commented:
<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
 
brettanderson77Author Commented:
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
 
brettanderson77Author Commented:
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
 
brettanderson77Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
just tried it and gives me

EOE Project Code      ConCustomer
A1                       Name1,Name2,Name3
0
 
harfangCommented:
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
 
brettanderson77Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 9
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now