Surveyor1
asked on
Concatenate records as a single fields
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
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
ASKER
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(sq l)
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
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(sq
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
ASKER
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
do I just insert SQL into a new query or do I insert into my orginal query ???
Regards
Anthony
CodeErrorMessage.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
add to your references Microsoft DAO x.x Object library
or better upload a copy of your db
or better upload a copy of your db
see this sample db, run query2
DB-Q-27636660.mdb
DB-Q-27636660.mdb
ASKER
Great Added the missing Refrences Works great !!!!! :-)
Many Thanks
Anthony
Many Thanks
Anthony
You might also want to consider the function DConcat, which I posted to my article https://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:
Add that function to a regular VBA module in your project, and then use a query like this:
If, say, SWI is text and not numeric:
If you wanted, say, semicolons as the delimiter:
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
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];
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];
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];
Q-27636660.mdb
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(sq
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