Solved

Concatenate records as a single fields

Posted on 2012-03-17
9
456 Views
Last Modified: 2012-03-17
Hello Experts

Thank you in advance.  I am looking to concatenate mutiple records into one single record based on the following:

I have data based on

 Project.  This is identfied by a project ID
 Work Instruction [SWI] (You can have numerous SWI's per project)
 Daywork Number [DWI] (You can have numerous DWI's per SWI)

the current query (attached) produces the following data

ID (Project)              SWI                 DayworkNumber

2246                            1                      1000        
2246                            1                      1001
2246                            2                      1000
2247                            1                      1000                    
2247                            1                      1001
2247                            2                      1000

What I actually want to do is return data for both a form and report as foillows:

ID (Project)              SWI                 DayworkNumber

2246                              1                   1000, 1001
2246                              2                   1000
2247                              1                   1000, 1001
2247                              2                   1000


also I will sometimes filter on the ID so when reporting on a specific project (i.e 2246) the data would show for example

ID (Project)              SWI                 DayworkNumber

2246                              1                   1000, 1001
2246                              2                   1000


I have looked at several examples but I am struggling to make work for may tables and queries etc... I am fairly new to Access and VB

Many Thanks

Anthony
QueryResult1.JPG
Query1.JPG
0
Comment
Question by:Surveyor1
  • 4
  • 4
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37732538
place this function in a regular module


Option Compare Database
Option Explicit

Function ConcatThem(x, y) As String
Dim rs As DAO.Recordset, strTemp As String
Dim sql As String
sql = "SELECT * FROM tblProjects"
sql = sql & " Where ID=" & x & " and SW1=" & y
Set rs = CurrentDb.OpenRecordset(sql)

    rs.MoveFirst
 
    Do Until rs.EOF
      strTemp = strTemp & ", " & rs("DayWorkNumber")
      rs.MoveNext
    Loop

    ConcatThem = Mid(strTemp, 3)
End Function





then, run this query

SELECT ID, SW1, concatthem([ID],[SW1]) AS DayWorkNumbers
FROM tblProjects
GROUP BY ID, SW1, concatthem([ID],[SW1]);


Result

ID           SW1       DayWorkNumbers
2246      1      1000, 1001
2246      2      1000
2247      1      1000, 1001
2247      2      1000
0
 

Author Comment

by:Surveyor1
ID: 37732625
Hi thanks for your quick response

using the information on my attachments

do I change your "tbl project" to the table in my query which is "CMWDayworkTable" ??

Also my Query uses

"ID" for the project
"DWSWI" which is your "SW1" ?
"DayworkNumber"

so do I change the code for module as follows: ??

Option Compare Database
Option Explicit

Function ConcatThem(x, y) As String
Dim rs As DAO.Recordset, strTemp As String
Dim sql As String
sql = "SELECT * FROM CMWDayworkTable"
sql = sql & " Where ID=" & x & " and DWSWI=" & y
Set rs = CurrentDb.OpenRecordset(sql)

    rs.MoveFirst
 
    Do Until rs.EOF
      strTemp = strTemp & ", " & rs("DayworkNumber")
      rs.MoveNext
    Loop

    ConcatThem = Mid(strTemp, 3)
End Function

And do i change the query as follows ??


SELECT ID, DWSWI, concatthem([ID],[DWSWI]) AS DayworkNumber
FROM CMWDayworkTable
GROUP BY ID, DWSWI, concatthem([ID],[DWSWI]);

Hope this is clear and again many thanks in advance.

I have altred the code as per the field names on my attachments

Regards

Anthony
0
 

Author Comment

by:Surveyor1
ID: 37732633
Also I now get the following error (as per attachment)

do I just insert SQL into a new query or do I insert into my orginal query ???

Regards

Anthony
CodeErrorMessage.JPG
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 37732783
which line is highlighted when you get the error?

create a new query, with this


SELECT ID, DWSWI, concatthem([ID],[DWSWI]) AS DayworkNumbers
FROM CMWDayworkTable
GROUP BY ID, DWSWI, concatthem([ID],[DWSWI]);
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Surveyor1
ID: 37732860
Hi  Thanks for your response but still get the error message with your SQL

the line highlighted when the error message pops up is

Dim rs As DAO.Recordset     (***Note Highlight in blue***)

then wehen you close messgae box the following line is highlighted in yellow

Function ConcatThem(x, y) As String

Many Thanks

Anthony
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37732873
add to your references Microsoft DAO x.x Object library

or better upload a copy of your db
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37732933
see this sample db, run query2
DB-Q-27636660.mdb
0
 

Author Comment

by:Surveyor1
ID: 37733085
Great Added the missing Refrences Works great !!!!! :-)

Many Thanks

Anthony
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 37733120
You might also want to consider the function DConcat, which I posted to my article http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

It has a few more options.

Source code:

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 = "Asc", _
    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, if it is
    '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
    '   column gets sorted
    ' 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 & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")
        
    ' 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 = CurrentDb.OpenRecordset(SQL)
    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



Add that function to a regular VBA module in your project, and then use a query like this:


SELECT CMWDayworkTable.[ID], CMWDayworkTable.[SWI], DConcat("[DWSWI]","[CMWDayworkTable]","[ID] = " & [ID] & " And [SWI] = " & [SWI]) AS Concatenated
FROM CMWDayworkTable
GROUP BY CMWDayworkTable.[ID], CMWDayworkTable.[SWI]
ORDER BY CMWDayworkTable.[ID], CMWDayworkTable.[SWI];

Open in new window


If, say, SWI is text and not numeric:

SELECT CMWDayworkTable.[ID], CMWDayworkTable.[SWI], DConcat("[DWSWI]","[CMWDayworkTable]","[ID] = " & [ID] & " And [SWI] = '" & [SWI] & "'") AS Concatenated
FROM CMWDayworkTable
GROUP BY CMWDayworkTable.[ID], CMWDayworkTable.[SWI]
ORDER BY CMWDayworkTable.[ID], CMWDayworkTable.[SWI];

Open in new window


If you wanted, say, semicolons as the delimiter:

SELECT CMWDayworkTable.[ID], CMWDayworkTable.[SWI], DConcat("[DWSWI]","[CMWDayworkTable]","[ID] = " & [ID] & " And [SWI] = " & [SWI], "; ") AS Concatenated
FROM CMWDayworkTable
GROUP BY CMWDayworkTable.[ID], CMWDayworkTable.[SWI]
ORDER BY CMWDayworkTable.[ID], CMWDayworkTable.[SWI];

Open in new window

Q-27636660.mdb
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Outlook Free & Paid Tools
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

21 Experts available now in Live!

Get 1:1 Help Now