Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Concatinate feild values grouped by another field

Posted on 2006-11-15
18
Medium Priority
?
649 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 1920 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 80 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 120

Expert Comment

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

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

885 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