Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Concatenate records as a single fields

Posted on 2012-03-17
9
Medium Priority
?
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
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!

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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
 

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 120

Expert Comment

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

or better upload a copy of your db
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 93

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
New style of hardware planning for Microsoft Exchange server.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

597 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