<

"Domain Aggregate" for Concatenating Values by Group in Microsoft Access

Published on
85,801 Points
29,801 Views
45 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick
by Patrick G. Matthews

1. Introduction


A common request in the various database zones at Experts Exchange involves returning a concatenated list of the various items in ColumnB for each distinct value in ColumnA from a particular table or query.  For example:

I have a table that currently holds student subject enrolments with the following fields

Student_ID, Subject_ID
1001, 11001
1001, 11023
1001, 11049
1002, 11023
1002, 11046
1003, 11023
etc

So a student can appear in the list a variable number of times depending on how many subjects they are enrolled in. Some students may be in 10 subjects some maybe in as little as one. I want to be able to transpose the information stored in this table and export it in a linear format something similar to.

Student 1, subject 1, subject 2, subject 3 ... subject n
student 2, subject 1, ,,,subject n

Like other databases, Access does not offer native functions that will do this.  One could use subqueries to return columns for each subject as above.  However, the following limitations apply:
In a Jet SQL solution, you would have to know how many detail values to allow for, and you could not simply let the SQL statement determine it dynamically
You could use VBA to dynamically generate a SQL statement, but that is beyond the skill of beginning Access developers, and many intermediate developers as well
Any such scheme relying on subqueries would require a column providing an ordinal for the detail items within the group

To overcome these obstacles, and to make it easier for most developers to seamlessly integrate this sort of functionality into their Access projects, I decided to create a UDF patterned after the more familiar "domain aggregate" functions such as DSum or DCount. So, the "DConcat" function was created.  As the name suggests, this function concatenates the values of a detail column (or detail columns) based on the groupings suggested by criteria the user supplies.

2. The DConcat() Function


Here is the VBA source code for DConcat:

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
    Optional Limit As Long = 0)
    
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
    
    ' Requires reference to Microsoft DAO library
    
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
    
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, if it is
    '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
    '   column gets sorted
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
    
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
    
    On Error GoTo ErrHandler
    
    ' Initialize to Null
    
    DConcat = Null
    
    ' Build up a query to grab the information needed for the concatenation
    
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")
        
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
    
    Set rs = CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF
            
            ' Initialize variable for this row
            
            ThisItem = ""
            
            ' Concatenate columns on this row
            
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next
            
            ' Trim leading delimiter
            
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            
            ' Concatenate row result to function return value
            
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
    
    ' Trim leading delimiter
    
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    
    GoTo Cleanup

ErrHandler:
    
    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
    
    DConcat = CVErr(Err.Number)
    
Cleanup:
    Set rs = Nothing
    
End Function

Open in new window


The DConcat function will produce a concatenated list of all of the values for the specified column(s) in a specified table or query pertaining to the records that meet the criteria specified in the Criteria argument (or all rows in the table/query if no criteria are specified).

The DConcat function takes the following arguments:
ConcatColumns are the detail columns whose values are to be concatenated.  This will usually be a single column, but can be multiple columns.  If more than one column, use commas to delimit the column names.  If column names contain anything other than letters, numerals, or underscores, column names must be enclosed in square brackets
Tbl is the name of table or query from which the data are taken.  If the table/query name contains anything other than letters, numerals, or underscores, the name must be enclosed in square brackets
(Optional) Criteria specifies the criteria to be applied in selecting and grouping the detail information.  To specify multiple criteria, and And or Or.  Be sure to use single-quotes around text values and # around dates/times.  I recommend using square brackets around all column names
(Optional) Delimiter1 is the delimiter to be used when concatenating each result row to the return value.  The default value is comma + space
(Optional) Delimiter2 is the delimiter to be used when concatenating the values in the ConcatColumns for each result row.  The default value is comma + space
(Optional) Distinct indicates whether all instances of the detail information is returned, or just the distinct items.  True by default
(Optional) Sort indicates whether the detail data are sorted before concatenation.  Use "Asc" for ascending (default) or "Desc" for descending.  Any other values are ignored.  Please note that if multiple columns are specified in ConcatColumns and if you use
(Optional) Limit specifies an upper limit for how many detail rows are concatenated.  If less than one or omitted, then no limit is applied

Each time DConcat is called, the code will run a query structured like this:

SELECT {DISTINCT} {TOP <Limit>} <ConcatColumns>
FROM <Tbl>
{WHERE <Criteria>}
{ORDER BY <ConcatColumns> Asc|Desc}

<xxx> ---> function arguments
{xxx} ---> optional items

Open in new window


The code then loops through that result set:
If ConcatColumns specifies more than one column, the column values for each returned row are concatenated using the Delimiter2 value as the delimiter
Those concatenated rows are then concatenated into a larger string, using the Delimiter1 value as the delimiter

3. Implementing DConcat in Your Access Projects


To implement DConcat in your Access project, all you need to do is add the source code above to a "regular" VBA module (i.e., not a class module, nor a code module for a Form or Report).  Once you have done this, you will be free to use the function in your queries, forms, or reports in the Access UI, or you may call DConcat from your other procedures in the VBA project.

4. Examples



 DConcat.mdb

The attached sample file, "DConcat.mdb", includes sample data for you to practice using this function.  The database includes the following five example queries:

Simple list of all accounts (qryAllAccounts)

SELECT DConcat("Account","Sample") AS Accounts
FROM Sample
GROUP BY DConcat("Account","Sample");


Returns:
Accounts
-----------------------------------------------------------------------------
Acct1, Acct10, Acct2, Acct3, Acct4, Acct5, Acct6, Acct7, Acct8, Acct9

Open in new window


List of projects for each account (qryProjectsByAcct)

SELECT Account, DConcat("ProjectNum","Sample","[Account] = '" & [Account] & "'") AS Projects
FROM Sample
GROUP BY Account;


Returns:
Account   Projects
-----------------------------------------------------------------------------
Acct1     11111, 49632
Acct10    50396
Acct2     38278, 42987, 51304
Acct3     42805, 48450, 51853, 51994
Acct4     50167, 51908
Acct5     50819
Acct6     51906
Acct7     54883
Acct8     53683
Acct9     55444

Open in new window


List of users for each account and project (qryUsersByAcctAndProject)

SELECT Account, ProjectNum, DConcat("User","Sample","[Account] = '" & [Account] & 
    "' And [ProjectNum] = " & [ProjectNum]) AS Users
FROM Sample
GROUP BY Account, ProjectNum;


Returns:
Account   ProjectNum   Users
-----------------------------------------------------------------------------
Acct1     11111        User2
Acct1     49632        User1, User2
Acct10     50396        User1
Acct2     38278        User1, User13, User15, User17, User18, User2, User3, User4, User5, User8
Acct2     42987        User1, User10, User13, User15, User2, User3, User5, User8
Acct2     51304        User1
Acct3     42805        User1, User16, User2, User5, User6
Acct3     48450        User1, User3, User5
Acct3     51853        User1
Acct3     51994        User1
Acct4     50167        User1, User7
Acct4     51908        User1, User3
Acct5     50819        User1, User10, User8, User9
Acct6     51906        User1, User3
Acct7     54883        User1, User3
Acct8     53683        User1, User10, User11, User12, User13, User3
Acct9     55444        User1, User10, User12, User14

Open in new window


List of tasks and users by project (qryTasksAndUsersByProject)

SELECT ProjectNum, DConcat("Task,User","Sample","[ProjectNum] = " & [ProjectNum],
    "; ",", ") AS Users
FROM Sample
GROUP BY ProjectNum;


Returns:
ProjectNum   Users
-----------------------------------------------------------------------------
11111        Project Management, User2
38278        Analysis & Model Config Design, User1; Analysis & Model Config Design, User2; Benchmarking Analysis & Findings, User1; Benchmarking Analysis & Findings, User2; Benchmarking Model Building, User1; Benchmarking Model Building, User2; Central Administrator Training, User13; Data Collection, User1; Data Collection, User2; Data Collection Prep, User1; Data Integration, User1; Data Integration, User2; Data Integration, User3; Findings and Recommendations, User1; Findings and Recommendations, User2; Implementation Study, User1; Implementation Study, User15; Implementation Study, User17; Implementation Study, User2; Implementation Study Prep, User1; Implementation Study Prep, User15; Implementation Study Prep, User17; Implementation Study Prep, User2; Implementation Study Prep, User8; Installation, User4; Model Building, User1; Model Building, User2; Model Building, User5; Model Design, User1; Model Design, User2; Planning, User1; Planning, User2; Study Analysis and Findings, User1; Study Analysis and Findings, User18; Study Analysis and Findings, User2
42805        Analysis Add On, User1; Analysis and Model Configuration Design, User2; Analysis and Model Configuration Design, User6; Data Collection, User16; Data Collection, User2; Data Collection, User6; Data Collection Preparation, User16; Data Collection Preparation, User2; Data Collection Preparation, User6; Model Template Delivery and Orientation, User1; Model Template Review, User1; Model Template Review, User5; POC Analysis and XML File Delivery, User1; POC Model Load, User1
42987        Data Integration, User1; Data Integration, User2; Data Integration, User3; Findings and Recommendations, User1; Findings and Recommendations, User2; Installation, User1; Model Building, User1; Model Building, User2; Model Building, User5; Model Configuration Design, User1; Model Configuration Design, User2; Planning, User1; Planning, User2; Rollout - First Market Findings, User1; Rollout - Preparation/Planning, User1; Rollout - Preparation/Planning, User2; Rollout - Support First Market, User1; Rollout - Support First Market, User15; Rollout - Support First Market, User8; WFM - Develop Scheduling Program, User1; WFM - Develop Scheduling Program, User2; WFM - Process Design, User1; WFM - Process Design, User2; WFM - Tool Construction, User1; WFM - Tool Construction, User2; WFM - Tool Construction, User5; WFM - Tool Installation & Training, User1; WFM - Tool Installation & Training, User10; WFM - Tool Installation & Training, User13
48450        Analysis Support, User1; Data Integration, User1; Data Integration, User3; Existing Model Information Capture, User1; Extended Model QA, User1; Matrix, User1; Model Build, User1; Model Build, User5; Model Design, User1; Model Design, User5; Model QA, User1; MSI Delivery, User1; Partial Model Delete, User1; Planning, User1; Role Allocation Matrix Revision, User1; Variance Report I, User1; Variance Report II, User1
49632        Project Management, User2; WES PM, User1
50167        Configuration, User1; Configuration, User7; Final Analysis, User1; Planning and Approach, User7; POC Monitoring, User1; POC Monitoring, User7
50396        WES Project Manager, User1
50819        Analysis and Model Configuration Design, User1; Call Center (Branch) Set-Up and Configuration, User1; Data Collection, User1; Data Collection, User8; Data Collection Prepartation, User1; End User Training, User10; Forecast Build, User9; Forecast Install, User9; Plan Install, User9; Planning, User9; Project Planning, User1; Schedule Build, User9; Schedule Install, User9
51304        Transactions Report, User1
51853        Auto Generate Reports, User1; Consolidate Report Date, User1; Extract Branch Attributes, User1; Monitor Report Progress, User1; Scheduling Query, User1
51906        Configuration, User1; DAM, User3; Planning and Approach, User1
51908        DAM, User1; DAM, User3; Foundation, User1; Foundation, User3; Planning and Approach, User1; Process Analysis Configuration, User1
51994        ABR Analysis (Change Order IIII - 54644), User1; Change In Sales Averaging Period, User1; Change In Wait Time Analysis, User1; CSR Team Consolidation (Change Order II - 54134), User1; Extended Wait Time Pilot (Change Order I - 54135), User1; Wait Time Pilot Prepartation, User1; Wait Time Pilot Support and Data Analysis, User1
53683        Analysis and Model Configuration Design, User1; Data Collection, User1; Data Collection Preparation, User1; Forecast Build, User11; Forecast Build, User3; Forecasting Training, User13; Planning, User1; Planning, User11; Planning, User3; Scheduling, User12; Train the Trainer Training, User10
54883        DAM, User1; DAM, User3; Process Analysis Configuration, User1
55444        Adherence Adapter Development and Test, User1; Adherence Adapter Development and Test, User12; Analysis and Model Configuration Design, User1; Central User Training, User14; Forecast, User14; Forecast Build, User1; Forecast Build, User12; Forecast Build, User14; Planning, User1; Scheduling, User1; Scheduling, User12; Scheduling, User14; Train the Trainer Training, User10

Open in new window


List of up to 3 tasks and users by project (qryTasksAndUsersByProjectLimit3)

SELECT ProjectNum, DConcat("Task,User","Sample","[ProjectNum] = " & [ProjectNum],
    "; ",", ",True,"Asc",3) AS Users
FROM Sample
GROUP BY ProjectNum;


Returns:
ProjectNum	Users
-----------------------------------------------------------------------------
11111        Project Management, User2
38278        Analysis & Model Config Design, User1; Analysis & Model Config Design, User2; Benchmarking Analysis & Findings, User1
42805        Analysis Add On, User1; Analysis and Model Configuration Design, User2; Analysis and Model Configuration Design, User6
42987        Data Integration, User1; Data Integration, User2; Data Integration, User3
48450        Analysis Support, User1; Data Integration, User1; Data Integration, User3
49632        Project Management, User2; WES PM, User1
50167        Configuration, User1; Configuration, User7; Final Analysis, User1
50396        WES Project Manager, User1
50819        Analysis and Model Configuration Design, User1; Call Center (Branch) Set-Up and Configuration, User1; Data Collection, User1
51304        Transactions Report, User1
51853        Auto Generate Reports, User1; Consolidate Report Date, User1; Extract Branch Attributes, User1
51906        Configuration, User1; DAM, User3; Planning and Approach, User1
51908        DAM, User1; DAM, User3; Foundation, User1
51994        ABR Analysis (Change Order IIII - 54644), User1; Change In Sales Averaging Period, User1; Change In Wait Time Analysis, User1
53683        Analysis and Model Configuration Design, User1; Data Collection, User1; Data Collection Preparation, User1
54883        DAM, User1; DAM, User3; Process Analysis Configuration, User1
55444        Adherence Adapter Development and Test, User1; Adherence Adapter Development and Test, User12; Analysis and Model Configuration Design, User1

Open in new window




=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
45
Comment
  • 9
  • 4
  • 4
  • +7
26 Comments
 
LVL 51

Expert Comment

by:Keith Alabaster
Cracking - yes from me
0
 
LVL 93

Author Comment

by:Patrick Matthews
All,

EE's latest supernova, cyberkiwi, helped me tease out an error condition I had never seen before.  We discuss it in the coda to this question involving DConcat.

Basically, unless you take some precautions, if a value in one of your criteria columns has a single-quote in it, that can cause DConcat to return an error: the dynamic SQL will be malformed unless the singlequotes are properly escaped.

Fortunately, the fix is pretty easy.  Instead of this:

SELECT HasSingleQuote, 
    DConcat("[AnotherColumn]", "[SomeTable", "[HasSingleQuote] = '" & [HasSingleQuote] & "'") AS List
FROM SomeTable
GROUP BY HasSingleQuote
ORDER BY HasSingleQuote

Open in new window


use this:

SELECT HasSingleQuote, 
    DConcat("[AnotherColumn]", "[SomeTable", "[HasSingleQuote] = '" & Replace([HasSingleQuote], "'", "''") & "'") AS List
FROM SomeTable
GROUP BY HasSingleQuote
ORDER BY HasSingleQuote

Open in new window


Cheers,

Patrick
0
 

Expert Comment

by:Chuck Morrison
I would like to send you money.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
LVL 93

Author Comment

by:Patrick Matthews
MarxChuck,

Glad you liked it!

If you're serious, my address is in my profile :)

Patrick
0
 

Expert Comment

by:phoenxMan
I am getting a "data type mismatch" when running on my data.  

I will send over a sample of my data when I see that you can look at it.

The data to be concatenated horizontal are text fields and some unique identifier Partnumber may have 300 detail record to flatten.

Here is a screencast video showing the issue.


I attached a copy of the Access database as well
ConcatFitmentsSample.accdb
0
 
LVL 93

Author Comment

by:Patrick Matthews
phoenxMan,

I do not see a screencast attached above.

In any event, can you post an MDB version of the file?  My main computer does not have Access 2007 installed.

Patrick
0
 

Expert Comment

by:phoenxMan
I am getting a "data type mismatch" when running on my data.  

I will send over a sample of my data when I see that you can look at it.

The data to be concatenated horizontal are text fields and some unique identifier Partnumber may have 300 detail record to flatten.

Here is a screencast video showing the issue.


I attached a copy of the Access database as well

HERE IS THE LINK:
http://screencast.com/t/YzdhZmU2ZGI

DconcatFitmentsSample.mdb
0
 
LVL 93

Author Comment

by:Patrick Matthews
phoenxMan,

I think I see the problem: your GetTRPart column is text, and so you have to encapsulate it in quotes when you pass it as a criterion.

If you change your query to:

SELECT [_HoldPartsFitmentsData].GetTRPart, DConcat("GETFitment","_HoldPartsFitmentsData","[GetTRPart] = '" & [GetTRPart] & "'","; ") AS Expr1
FROM _HoldPartsFitmentsData
GROUP BY [_HoldPartsFitmentsData].GetTRPart, DConcat("GETFitment","_HoldPartsFitmentsData","[GetTRPart] = '" & [GetTRPart] & "'","; ");

Open in new window


then it runs successfully.

I addressed this issue in Section 2, in the bullet for the Criteria argument, and demonstrated this in the example qryProjectsByAcct in the sample database.  Reprinting that here:

SELECT Account, DConcat("ProjectNum","Sample","[Account] = '" & [Account] & "'") AS Projects
FROM Sample
GROUP BY Account

Open in new window


Please give that a try, and let me know if it works for you :)

Patrick
0
 

Expert Comment

by:phoenxMan
It is getting the concatenated details but crops after 256 characters.  See video.

http://screencast.com/t/OTU0YWQ5N

Your sample also has long strings returned exceeding 256 characters

0
 
LVL 93

Author Comment

by:Patrick Matthews
Sorry about that!

The problem, I think, is that the DConcat expression should not have been included in the GROUP BY; any time you include a column in the GROUP BY clause, Access will truncate it to 255 characters.  So, try:

SELECT [_HoldPartsFitmentsData].GetTRPart, DConcat("GETFitment","_HoldPartsFitmentsData","[GetTRPart] = '" & [GetTRPart] & "'","; ") AS Expr1
FROM _HoldPartsFitmentsData
GROUP BY [_HoldPartsFitmentsData].GetTRPart;

Open in new window


Cheers,

Patrick
0
 

Expert Comment

by:phoenxMan
I may have found the solutions to cropping of

http://screencast.com/t/ZWM4MTFjZmEt
0
 

Expert Comment

by:Dare626
Worked wonderfully Patrick, although i still can't figure out the inner workings of how it comes up with the results due to my lack of VBA it works wonderfully. THanks for you're contribution to this community.

-Julio Quijano in Calirnia.
0
 
LVL 47

Expert Comment

by:aikimark
@Patrick

Most excellent article and DConcat() routine.  Thanks.

========
I needed to use this function in one of my current projects.  I thought I'd post the routine with my tweaks (below).
* changed from currentdb to dbengine(0)(0) as source for recordset instantiation
* used forward-only as (explicit) open database type
* removed a few "rs" recordset references within the With...End With structure

Note: I also tested the following field iteration, but saw no substantive performance difference.  It tested just slightly less well than the numeric field (index) reference code, most probably due to a field variable allocation.
For Each fld In .Fields
  ThisItem = ThisItem & Delimiter2 & Nz(fld.Value, "")
Next

Open in new window

Public Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
    Optional Limit As Long = 0)
    
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
    
    ' Requires reference to Microsoft DAO library
    
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
    
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, if it is
    '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
    '   column gets sorted
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
    
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
    
    On Error GoTo ErrHandler
    
    ' Initialize to Null
    
    DConcat = Null
    
    ' Build up a query to grab the information needed for the concatenation
    
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")
        
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
    
    Set rs = DBEngine(0)(0).OpenRecordset(SQL, dbOpenForwardOnly)  'WAS CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF
            
            ' Initialize variable for this row
            
            ThisItem = ""
            
            ' Concatenate columns on this row
            
            For FieldCounter = 0 To .Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(.Fields(FieldCounter).Value, "")
            Next
            
            ' Trim leading delimiter
            
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            
            ' Concatenate row result to function return value
            
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
    
    ' Trim leading delimiter
    
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    
    GoTo Cleanup

ErrHandler:
    
    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
    
    DConcat = CVErr(Err.Number)
    
Cleanup:
    Set rs = Nothing
    
End Function

Open in new window

0
 
LVL 93

Author Comment

by:Patrick Matthews
Thanks for that feedback, Mark, and I am glad you found it useful!

One of these days I will break my habit of using CurrentDb, just as someday I rid myself of the habit of using DoCmd.RunSQL instead of DbExecute :)

As for enumerating via index as opposed to enumerating with For...Each...Next, it is typically the case that For...Each...Next is slower for just about any collection.  Even so, my own opinion is that it makes the code more self-documenting, so in cases where I will never notice the performance hit I tend to use it anyway.

Cheers,

Patrick
0
 
LVL 93

Author Comment

by:Patrick Matthews
I have tweaked the function a bit, to do two things:

1) Incorporate aikimark's suggestion to use DBEngine(0)(0) instead of CurrentDB

2) Change the Sort argument to basically be a stand-in for an ORDER BY clause

Enjoy!

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "", _
    Optional Limit As Long = 0)
    
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
    
    ' Requires reference to Microsoft DAO library
    
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
    
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, the
    '   columns used for the sort.  As you would in an ORDER BY clause, use Asc or Desc to
    '   indicate whether the column is sorted ascending or descending.  If Asc/Desc is
    '   omitted, Asc is assumed by the query engine.  Note that if you use any columns
    '   in the Sort argument that are not in the ConcatColumns argument, you must use
    '   False for the Distinct argument
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
    
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
    
    On Error GoTo ErrHandler
    
    ' Initialize to Null
    
    DConcat = Null
    
    ' Build up a query to grab the information needed for the concatenation
    
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        IIf(Sort <> "", "ORDER BY " & Sort, "")
        
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
    
    Set rs = DBEngine(0)(0).OpenRecordset(SQL, dbOpenForwardOnly)
    With rs
        Do Until .EOF
            
            ' Initialize variable for this row
            
            ThisItem = ""
            
            ' Concatenate columns on this row
            
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next
            
            ' Trim leading delimiter
            
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            
            ' Concatenate row result to function return value
            
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
    
    ' Trim leading delimiter
    
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    
    GoTo Cleanup

ErrHandler:
    
    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
    
    DConcat = CVErr(Err.Number)
    
Cleanup:
    Set rs = Nothing
    
End Function

Open in new window


This allows greater control over how the results are sorted.  For example, all of the following are valid now for the Sort argument:

"[ColumnA] , [ColumnB] , [ColumnC]"
"[ColumnA] Asc, [ColumnB] Asc, [ColumnC] Asc"
"[ColumnA] Desc, [ColumnB] Desc, [ColumnC] Desc"
"[ColumnA] Asc, [ColumnB] Desc, [ColumnC] Asc"

etc.
0
 

Expert Comment

by:developingprogrammer
superb code!!!!
0
 

Expert Comment

by:developingprogrammer
hi matthewspatrick, thanks for your fantastic function!

could you help me take a look at my question "Runtime 3420 problem - DConcat function problem"?

i suspect it's something to do with dconcat cause my code does not touch any recordset at all but it starts to show error# all the way.

i've uploaded the database and all the pictures. thanks for your help matthewspatrick! = )
0
 

Expert Comment

by:developingprogrammer
hi matthewspatrick!

just some feedback. in a query with a single table, this works.

CodeDefinition: DConcat("Acronym","adminCodeList_BU1","","-","=","False","CategoryNumber Asc")

Open in new window


but this doesn't

CodeDefinition: DConcat("Acronym","adminCodeList_BU1","","-","=","True","CategoryNumber Asc")

Open in new window


the difference is the Distinct argument. False works but True does not.

however if i were to remove the CategoryNumber sort, then it works.

i am not sure as i'm fairly (meaning very haha) new to databases, perhaps the instances are required for sorting to happen.

if you've got some time perhaps you could share with us? or maybe i'm doing something wrong! sorry if i am! thanks in advance matthewspatrick!
0
 
LVL 93

Author Comment

by:Patrick Matthews
It's a SQL limitation.  If you use "SELECT DISTINCT ...", then if you also use an ORDER BY clause the ORDER BY can only refer to columns in the SELECT clause.
0
 

Expert Comment

by:developingprogrammer
Ah that makes sense. Thanks matthewspatrick! = )
0
 

Expert Comment

by:itsquad
Patrick!  Nice work maing!  I've been using your code on this for more than a few years now - thank you!  Quick question, the Delimiter says (Optional) ... how can I change the delimiter from a "," to ";"?  Thanks, Kevin
0
 
LVL 93

Author Comment

by:Patrick Matthews
The Optional means that if you omit that argument in the function call, then the default value is used (which, in the case of the delimiters, is a comma followed by a space). If you want to use a different delimiter, just make sure to supply that argument in the function call.
0
 

Expert Comment

by:Pedro Pinto
Congrats, great function!

A simple (and possibly stupid) question.

Regarding the query   qryUsersByAcctAndProject  in the DConcat.mdb sample database

How should one use the quotes and double quotes (' , ") If the ProjectNum field was a text instead of a number?

query (ProjectNum as number)
Users: DConcat("User","Sample","[Account] = '" & [Account] & "' And [ProjectNum] = " & [ProjectNum]) AS Users

query (ProjectNum as text)
.....

I've tried some combinations of this query with a text but I'm always stucked and a bit confused with the syntax and alwaus get an error

Users: DConcat("User","Sample","[Account] = '" & [Account] & "' And &"[ProjectNum] = '" & [ProjectNum]"') AS Users

Thanks,
Best regards,
Pedro
0
 

Expert Comment

by:G Engwall
The Bomb! Solved a major headache for me. Thanks.
0
 

Expert Comment

by:itsquad
Hi Patrick - thanks for this!!!  Very helpful!  
Bit of a novice I am, but I have a question.

DConcat("IMAGEORIG","Search_Windows_TBL","[Value1] = '" & [Value1] & "'")

My DConcat gets truncated to the 255 character limitation in it's results.  

UPDATE:  Sorry, I read the rest of the threads and found that when you set the field to "Expression" instead of "Groupby" - then it provides the full results.  -  Thanks again Patrick!
0
 
LVL 47

Expert Comment

by:aikimark
This is really weird.  I'm populating a recordset from the second and third sample queries.  The qryProjectsByAcct query returns expected (published) results.  However, the qryTasksAndUsersByProject query shows garbage characters in the DConcat() field when the DConcat() results are greater than 255 characters.  The function is returning the correct string.  Somehow, the query result isn't populating the recordset correctly.
Note: The query datasheet view shows correct results.
Note: I'm running this in an Access 2007 environment.
* * * * qryProjectsByAcct * * * *
Acct1         11111, 49632
Acct10        50396
Acct2         38278, 42987, 51304
Acct3         42805, 48450, 51853, 51994
Acct4         50167, 51908
Acct5         50819
Acct6         51906
Acct7         54883
Acct8         53683
Acct9         55444

* * * * qryTasksAndUsersByProject * * * *
 11111        Project Management, User2
 38278        Analysis & Model Config Design, User1; Analysis & Model Config Design, User2; Benchmarking Analysis & Findings, User1; Benchmarking Analysis & Findings, User2; Benchmarking Model Building, User1; Benchmarking Model Building, User2; Central Administrator T   ??? .??????    ???????    ??????? '    h?  ??   ????       ??????? ? ?????   ?????   ?? ????   ???  ??????  ?  ? ?    ?????????­???? I???
 ??????      ????????0 ????????? 0 ???  ??????????????????????   ?????????????  ??0   ??????????D ?<????   <????   :??? ?        
   ?  
   ?                   ????????    ??  ??    ?   ??     ?      :      :       ??  ???1??? ???????????  ??<???<??  ??        ?<?7????????<???<?  ????:  ?7?1  ?;???    ?  ??????  ? ??        ???7??????????????  ????:?7      ??      ?      ?      ?      ?    ???      ?      ?      ?      ?      ?    ???      ?      ?    ??      ?    ???    ??     ?    ???    ???    ???    ???    ??    ??  ?;??    ???      ?      ?  ?  ?  ???/?  ?;?
 42805        Analysis Add On, User1; Analysis and Model Configuration Design, User2; Analysis and Model Configuration Design, User6; Data Collection, User16; Data Collection, User2; Data Collection, User6; Data Collection Preparation, User16; Data Collection Preparati   ??? .??????    ???????    ??????? j    h?  ??   ????       ??????? ? ?????   ?????   ?? ????   ???  ??????  ?  ? ?    ?????????­???? I???
 ??????      ????????0 ????????? 0 ???  ??????????????
 42987        Data Integration, User1; Data Integration, User2; Data Integration, User3; Findings and Recommendations, User1; Findings and Recommendations, User2; Installation, User1; Model Building, User1; Model Building, User2; Model Building, User5; Model Configurat   ??? .??????    ???????    ??????? =    h?  ??   ????       ??????? ? ?????   ?????   ?? ????   ???  ??????  ?  ? ?    ?????????­???? I???
 ??????      ????????0 ????????? 0 ???  ??????????????????????   ?????????????  ??0   ??????????D ?<????   <????   :??? ?        
   ?  
   ?                   ????????    ??  ??    ?   ??     ?      :      :       ??  ???1??? ???????????  ??<???<??  ??        ?<?7????????<???<?  ????:  ?7?1  ?;???    ?  ??????  ? ??        ???7??????????????  ????:?7      ??      ?      ?      ?      ?    ???      ?      ?      ?      ?      ?    ???      ?      ?    ??      ?    ???
 48450        Analysis Support, User1; Data Integration, User1; Data Integration, User3; Existing Model Information Capture, User1; Extended Model QA, User1; Matrix, User1; Model Build, User1; Model Build, User5; Model Design, User1; Model Design, User5; Model QA, User   ??? .??????    ???????    ??????? Z    h?  ??   ????       ??????? ? ?????   ?????   ?? ????   ???  ??????  ?  ? ?    ?????????­???? I???
 ??
 49632        Project Management, User2; WES PM, User1
 50167        Configuration, User1; Configuration, User7; Final Analysis, User1; Planning and Approach, User7; POC Monitoring, User1; POC Monitoring, User7
 50396        WES Project Manager, User1
 50819        Analysis and Model Configuration Design, User1; Call Center (Branch) Set-Up and Configuration, User1; Data Collection, User1; Data Collection, User8; Data Collection Prepartation, User1; End User Training, User10; Forecast Build, User9; Forecast Install,    ??? .??????    ???????    ??????? ( #   h?  ??   ????       ??????? ? ?????   ?????   ?? ????   ???  
 51304        Transactions Report, User1
 51853        Auto Generate Reports, User1; Consolidate Report Date, User1; Extract Branch Attributes, User1; Monitor Report Progress, User1; Scheduling Query, User1
 51906        Configuration, User1; DAM, User3; Planning and Approach, User1
 51908        DAM, User1; DAM, User3; Foundation, User1; Foundation, User3; Planning and Approach, User1; Process Analysis Configuration, User1
 51994        ABR Analysis (Change Order IIII - 54644), User1; Change In Sales Averaging Period, User1; Change In Wait Time Analysis, User1; CSR Team Consolidation (Change Order II - 54134), User1; Extended Wait Time Pilot (Change Order I - 54135), User1; Wait Time Pil   ??? .??????    ???????    ???????  (   h?  ??   ????       ???
 53683        Analysis and Model Configuration Design, User1; Data Collection, User1; Data Collection Preparation, User1; Forecast Build, User11; Forecast Build, User3; Forecasting Training, User13; Planning, User1; Planning, User11; Planning, User3; Scheduling, User12   ??? .??????    ???????    ???
 54883        DAM, User1; DAM, User3; Process Analysis Configuration, User1
 55444        Adherence Adapter Development and Test, User1; Adherence Adapter Development and Test, User12; Analysis and Model Configuration Design, User1; Central User Training, User14; Forecast, User14; Forecast Build, User1; Forecast Build, User12; Forecast Build,    ??? .??????    ???????    ??????? K +   h?  ??   ????       ??????? ? ?????   ?????   ?? ????   ???  ??

Open in new window

Testing code here.  The vItem variable is the sample query name.
        Set rs = DBEngine(0)(0).OpenRecordset(vItem, dbOpenSnapshot)
        Debug.Print clsPerf.StopTimer, "Openrecordset"
        rs.MoveLast
        Debug.Print clsPerf.StopTimer, "Movelast"
        rs.MoveFirst
        clsPerf.StartTimer
        Do Until rs.EOF
            Debug.Print rs.Fields(0).Value, rs.Fields(1).Value
            rs.MoveNext
        Loop

Open in new window

Feel free to message me to explore/solve offline.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month