Query help, concatenating results from a 1 to many table

[JT in DC]

I have a table which joins two tables in a many-to-many relationship in
Access 2003. What I would like to know how to do is to get the results of
one of those columns in a concatenated string for each of the values in the
other column.

Example:
Col1 Col2
A 1
A 2
A 3
B 2
B 4
C 4
D 1
D 2

I would like results like

Col1 Col2
A "1,2,3"
B "2,4"
C "4"
D "1,4"

Any assistance would be appreciated. thanks.

 

[Ken Sheridan]

One way is to create the following function in your database:

Public Function GetList(strKey as String) As String

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strList As String

strSQL = "SELECT * FROM YourTable " & _
"WHERE Col1 = """ & strKey & """ " & _
"ORDER BY Col2"

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenForwardOnly

Do While Not .EOF
strList = strList & "," & .Fields("Col2")
.MoveNext
Loop
.Close
' remove leading comma
strList = Mid$(strList, 2)
End With

GetList = strList

End Function

You can then call the function in a query:

SELECT DISTINCT Col1, GetList(Col2) AS Col2List,
FROM YourTable;

You might also like to take a look at Jamie Collins post in the following
thread which includes a neat way of doing this by calling the GetString
method of an ADO recordset object:


http://www.microsoft.com/office/com...ffice-access&lang=en&cr=US&sloc=en-us&m=1&p=1


Ken Sheridan
Stafford, England

 

[Greg Helmbrecht]

Insert a new column into your query and build like:

Col3: [Col1] & "," & [Col2]

Think that should work