• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

Concatenating of sorts

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?

Thanks!
0
dgran
Asked:
dgran
1 Solution
 
dgranAuthor Commented:
Continued...

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.
0
 
Dale FyeCommented:
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
        rs.MoveNext
    Wend

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

Open in new window

0
 
Anthony BerenguelCommented:
drgan,

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

ab
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LIONKINGCommented:
Nevermind, thought it was SQL.
0
 
dgranAuthor Commented:
fyed,

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.

Thanks!
Concat.accdb
0
 
aikimarkCommented:
Here is an excellent article with code examples on the DConcate() function:
http:A_2380.html
0
 
Dale FyeCommented:
dgran,

Unable to download at the moment.  Will take a look this evening.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now