Solved

VBA Concatinate feild values grouped by another field

Posted on 2006-11-15
18
623 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Formatting Excel from Access with VBA... 5 57
Linking Master Child Fields 2 24
Access syntax 1 33
MS Access Error Handling 6 27
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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 …

713 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