?
Solved

Access

Posted on 2012-08-14
7
Medium Priority
?
653 Views
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.

Thanks!

Final-Access1.accdb
0
Comment
Question by:pauledwardian
  • 4
  • 3
7 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 38294047
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, "")
            Next
            
            ' Trim leading delimiter
            
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            
            ' Concatenate row result to function return value
            
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
    
    ' Trim leading delimiter
    
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    
    GoTo Cleanup

ErrHandler:
    
    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
    
    DConcat = CVErr(Err.Number)
    
Cleanup:
    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:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html
0
 

Author Comment

by:pauledwardian
ID: 38296683
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.
Final-Access1.accdb
Thank you
Paul
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38296849
Paul,

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

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

Patrick
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:pauledwardian
ID: 38296965
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

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38297021
Replace:

"[Trans Num]"

with:

"[Post Date], [Trans Num]"
0
 

Author Comment

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

Thank you again
0
 

Author Closing Comment

by:pauledwardian
ID: 38317518
Thanks alot. I wish you could also help me with replacing commas with dash.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Viewers will learn how the fundamental information of how to create a table.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

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