Solved

VBA Concatinate feild values grouped by another field

Posted on 2006-11-15
18
602 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
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.

 

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

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
HasData 9 37
Open a Specific Record With a Specific Tab Page 5 31
ms access 2013, running .mdb 2 32
Find missing numbers in Access Table PrimaryKey 9 15
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
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 …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

867 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

15 Experts available now in Live!

Get 1:1 Help Now