How can I take multiple rows and put them into one column with a delimiter in MS Access?

Ok, I am working on a priority project where I have a table that contains two fields. One is an ID field and another is TIF_NAMES field. So the table looks like this:

ID               TIF_NAMES
1                000001.tif
1                000002.tif
1                000003.tif
2                000004.tif
2                000005.tif

I would like to do this

ID                   TIF_NAMES
1                    000001.tif|000002.tif|000003.tif
2                    000004.tif|000005.tif

I need to do something that can go through my field and create a new table with the ID and TIF_NAMES and the data represented like l have shown above.

Thank you for any suggestions. My VBS does not work.
Laurie KingeDiscovery ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
The following article covers how to do this, and includes a very handy and flexible function, DConcat, for achieving this:

1) Add this code in a regular VBA module (i.e., not a form, report, or class module):

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, "")
            ' Trim leading delimiter
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            ' Concatenate row result to function return value
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
    End With
    ' Trim leading delimiter
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    GoTo Cleanup

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

Open in new window

2) Use it in a query like this:

SELECT [ID], DConcat("[TIF_NAMES]", "[YourTable]", "[ID] = " & [ID], "|") AS xTIF_NAMES
FROM [YourTable]

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:

did you look at the links i posted above?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.