Concatenating of sorts

Posted on 2012-08-20
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


    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 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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

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

    LVL 13

    Expert Comment

    Nevermind, thought it was SQL.

    Author Comment


    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 44

    Accepted Solution

    Here is an excellent article with code examples on the DConcate() function:
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now