Concatenate values from mutiple records into single column

  • Thread starter Thread starter Tom Currier
  • Start date Start date
T

Tom Currier

Table 1 = Example of Source table (contains around 5k records)
KeyField FieldtoCombine
524 EM
1547 MS
1621 MS
1621 TV
1691 MS
1691 OM
1696 MS

Table 2 = Example of the result I need to produce from the source
KeyField CombinedFields
524 EM
1547 EM
1621 MSTV
1691 MSOM
1696 MS
Table 1 may have up to 3 records with the same keyfield
I know how to do this using a cursor in SQL (T-SQL)
But not how to do in Access and I can't use SQL Server to solve this issue
This is in Access 2000
Thanks in advance for any assistance as this may trigger my brain to work
 
Tom said:
Table 1 = Example of Source table (contains around 5k records)
KeyField FieldtoCombine
524 EM
1547 MS
1621 MS
1621 TV
1691 MS
1691 OM
1696 MS

Table 2 = Example of the result I need to produce from the source
KeyField CombinedFields
524 EM
1547 EM
1621 MSTV
1691 MSOM
1696 MS
Table 1 may have up to 3 records with the same keyfield
I know how to do this using a cursor in SQL (T-SQL)
But not how to do in Access and I can't use SQL Server to solve this
issue This is in Access 2000
Thanks in advance for any assistance as this may trigger my brain to
work
Click to expand...
You will need to use a cursor (a recordset) in an Access VBA module. You
should post this to the vba group.

A possible query-based solution is to create a saved crosstab query and
then use another query to concatenate the pivot columns.

But it's probably easier to use a recordset.
 
Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with both ADO
and DAO. There are comments regarding which lines to comment or uncomment
based on which library you prefer. Access 97 is mostly DAO while the default
for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

You should be able to use an SQL statement like the following

SELECT KeyField
, Concat("SELECT [FieldtoCombine] FROM Table1 WHERE Keyfield=" & KeyField,"")
as Combined
FROM Table1

You will need to copy the Concat function into a VBA module in your database.

--
Duane Hookom
MS Access MVP

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Back
Top