Posted on 2012-08-14
Last Modified: 2012-08-21
I have the following access file avialble which expert exchange experts helped me out to figure it out. I have almost the same question. I included the vba and the querry that helped in my previous code.
On the attachment you can find the Table2 which is the inital table I have and here is how I need it to look like: (FYI: "Current" table is for the old question just to help with coding).
explenationFYI: The date for each number has to be the latest post date.
Also, the sequence of the Work Codes on each cell for each SRO Number (RCV, RCV....) has to be based on the Trans Date and Trans Number.


Question by:pauledwardian
    LVL 92

    Accepted Solution

    1) Add this function to a regular VBA 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
        ' 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 [SRO Num], 
        DConcat("[Work Code]", "[Table2]", "[SRO Num] = '" & [SRO Num] & "'", ", ", "",
            False, "[Trans Num]") AS WorkCodes, Max([Post Date]) AS PostDate
    FROM Table2
    GROUP BY [SRO Num];

    Open in new window

    For more info, please see my article:

    Author Comment

    Thanks a lot. But for instance on S34 the sequence should be HF, HF, HF, HF, HF TIG, TIG, AIR, TIG. The querry shows it as HF, HF, TIG, HF, TIG, HF, AIR, HF, TIG!
    Please take a look at the attachment.
    Thank you
    LVL 92

    Expert Comment

    by:Patrick Matthews

    I had mistakenly assumed that you wanted to sort the concatenated results based on the [Trans Num] column.  Based on your message above, it appears that you might want to sort on the [Post Date] or [Trans Date] columns instead.

    To sort on, say, [Trans Date], just replace "[Trans Num]" in the function call with "[Trans Date]".

    I did notice that for [SRO Num] = S000000168 and [SRO Num] = S000000351 you have some instances of multiple records with the same [Trans Date] value.  You can sort by multiple columns.  For example, to sort first by [Trans Date] and then by [Trans Num] then replace "[Trans Num]" with "[Trans Date], [Trans Num]".


    Author Comment

    Exactly, I need to sort the sequence by Transaction number and Post Date. Im sorry how can I change this querry to meet my requirment:

    SELECT [SRO Num], DConcat("[Work Code]", "[Table2]", "[SRO Num] = '" & [SRO Num] & "'", ", ", "",
            False, "[Trans Num]") AS WorkCodes, Max([Post Date]) AS PostDate
    FROM Table2
    GROUP BY [SRO Num];

    Open in new window

    LVL 92

    Expert Comment

    by:Patrick Matthews

    "[Trans Num]"


    "[Post Date], [Trans Num]"

    Author Comment

    Thank you very much. One last question! how can I replace the "," in the vba code to "-" (dash)?

    Thank you again

    Author Closing Comment

    Thanks alot. I wish you could also help me with replacing commas with dash.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    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.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    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