Concatenating of sorts

Posted on 2012-08-20
Medium Priority
Last Modified: 2012-08-28
I'm not sure if this is truly concatenating but here's what I'm trying to do.

I have this example table:

Column A            Column B
Fruit                     Apples
Fruit                     Oranges
Fruit                     Pears
Vegetables           Corn
Vegetables           Lettuce
Vegetables           Peas

I would like to write a query that will produce the following result table:

Column A             Column B
Fruit                      Apples, Oranges, Pears
Vegetables           Corn, Lettuce, Peas

What functions can I use? Is there a single or multiple functions out there that I can use to achieve this result?  Do I need to write a function using VBA or something to do this?

Question by:dgran

Author Comment

ID: 38312962

I will propably need to "Group by" multiple columns and "Concatenate" (if that's the proper term) multiple columns at the same time.  Don't know if this changes the answer on how to perform the task.
LVL 49

Expert Comment

by:Dale Fye
ID: 38313005
I use a function (below), and a query syntax that looks like:

SELECT T1.ColumnA, fnConCat("ColumnB", "yourTableName", , , "[ColumnA] = '" & T1.ColumnA & "'") as ColumnB
FROM (SELECT DISTINCT ColumnA FROM yourTableName) as T1

Note that I use a subquery to initially limit the values from ColumnA, then generate the concatenated data from columnB.
Public Function fnConcat(FieldName As String, TableName As String, _
                         Optional Delimeter As String = ",", _
                         Optional Wrapper As String = "", _
                         Optional Criteria As Variant = Null) As String

    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim varConcat As Variant
    strSQL = "SELECT [" & FieldName & "] " _
           & "FROM [" & TableName & "] " _
           & ("WHERE " + Criteria)
    Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
    varConcat = Null
    While Not rs.EOF
        If Not IsNullOrBlank(rs(0)) Then
            varConcat = (varConcat + Delimeter) & (Wrapper + rs(0) + Wrapper)
        End If

    fnConcat = Nz(varConcat, "")
    If Not rs Is Nothing Then
        Set rs = Nothing
    End If
    Exit Function
    Call DisplayError("Something failed in the concatenation function")
    Resume ConcatExit
End Function

Open in new window

LVL 10

Expert Comment

by:Anthony Berenguel
ID: 38313008

if you provide the table names along with their fields names I can write a VBA function that will return the values in the layout you're looking for...

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 13

Expert Comment

ID: 38313039
Nevermind, thought it was SQL.

Author Comment

ID: 38314164

I keep getting an error "Undefined Function...".  I've copied the vbscript above and created a module.  I've attached my test database for your review.

LVL 46

Accepted Solution

aikimark earned 2000 total points
ID: 38317370
Here is an excellent article with code examples on the DConcate() function:
LVL 49

Expert Comment

by:Dale Fye
ID: 38317609

Unable to download at the moment.  Will take a look this evening.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

850 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