Combining rows

B

Bltony

Good day everyone,

I have a table as the followings:
Subs Name Date Serv Customer Service Rep
AAA 10/25/05 Barbara
AAA 10/30/05 Shirley
AAA 11/02/05 John
BBB 9/08/05 Jack
BBB 12/1/05 John
BBB 12/10/05 Barbara

I would like to tract the names of Customer Service Rep for each subscriber.
For the table above, I would like to have the result as:

AAA Barbara, Shirley, John
BBB Jack, John, Barbara

Please help. Many thanks.
 
D

Duane Hookom

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Your expression might be
SELECT [Subs Name], Concatenate("SELECT [Customer Service Rep] FROM [as the
followings] WHERE [Subs Name] ='" & [Subs Name] & "'") As AllCustReps
FROM [as the followings]
GROUP BY [Subs Name], Concatenate("SELECT [Customer Service Rep] FROM [as
the followings] WHERE [Subs Name] ='" & [Subs Name] & "'");
 
B

Bltony

Hi Duane, thanks so much for your help. However, when I ran the query, it
said "undefined function 'Concatenate' in expression.

Here is my SQL:
SELECT [my table].[Subs Name], Concatenate("SELECT [my table].[Customer
Service Rep] FROM [my table] WHERE [my table].[Subs Name] ='" & [my
table].[Subs Name] & "'") As AllCustReps
FROM [my table]
GROUP BY [my table].[Subs Name], Concatenate("SELECT [my table].[Customer
Service Rep] FROM [my table] WHERE [my table].[Subs Name] ='" & [my
table].[Subs Name] & "'");

Please review and see if I missed something. Thanks Duane.


Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Your expression might be
SELECT [Subs Name], Concatenate("SELECT [Customer Service Rep] FROM [as the
followings] WHERE [Subs Name] ='" & [Subs Name] & "'") As AllCustReps
FROM [as the followings]
GROUP BY [Subs Name], Concatenate("SELECT [Customer Service Rep] FROM [as
the followings] WHERE [Subs Name] ='" & [Subs Name] & "'");

--
Duane Hookom
MS Access MVP
--

Bltony said:
Good day everyone,

I have a table as the followings:
Subs Name Date Serv Customer Service Rep
AAA 10/25/05 Barbara
AAA 10/30/05 Shirley
AAA 11/02/05 John
BBB 9/08/05 Jack
BBB 12/1/05 John
BBB 12/10/05 Barbara

I would like to tract the names of Customer Service Rep for each
subscriber.
For the table above, I would like to have the result as:

AAA Barbara, Shirley, John
BBB Jack, John, Barbara

Please help. Many thanks.
Click to expand...
Click to expand...
 
D

Duane Hookom

Did you import/paste the module/function into your MDB file? The sample file
contains a function in a module that must be imported or pasted into your
mdb for the solution to work.

--
Duane Hookom
MS Access MVP
--

Bltony said:
Hi Duane, thanks so much for your help. However, when I ran the query, it
said "undefined function 'Concatenate' in expression.

Here is my SQL:
SELECT [my table].[Subs Name], Concatenate("SELECT [my table].[Customer
Service Rep] FROM [my table] WHERE [my table].[Subs Name] ='" & [my
table].[Subs Name] & "'") As AllCustReps
FROM [my table]
GROUP BY [my table].[Subs Name], Concatenate("SELECT [my table].[Customer
Service Rep] FROM [my table] WHERE [my table].[Subs Name] ='" & [my
table].[Subs Name] & "'");

Please review and see if I missed something. Thanks Duane.


Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Your expression might be
SELECT [Subs Name], Concatenate("SELECT [Customer Service Rep] FROM [as
the
followings] WHERE [Subs Name] ='" & [Subs Name] & "'") As AllCustReps
FROM [as the followings]
GROUP BY [Subs Name], Concatenate("SELECT [Customer Service Rep] FROM [as
the followings] WHERE [Subs Name] ='" & [Subs Name] & "'");

--
Duane Hookom
MS Access MVP
--

Bltony said:
Good day everyone,

I have a table as the followings:
Subs Name Date Serv Customer Service Rep
AAA 10/25/05 Barbara
AAA 10/30/05 Shirley
AAA 11/02/05 John
BBB 9/08/05 Jack
BBB 12/1/05 John
BBB 12/10/05 Barbara

I would like to tract the names of Customer Service Rep for each
subscriber.
For the table above, I would like to have the result as:

AAA Barbara, Shirley, John
BBB Jack, John, Barbara

Please help. Many thanks.
Click to expand...
Click to expand...
Click to expand...
 
B

Bltony

Hi Duane,
I am sorry I don't have a lot of experiences in Access, so would you please
walk with me step by step to explain what do you mean by import or paste into
my MDB file.

I have a table as first posted and I did a SQL query as I just posted. Now
what do I need to do? Please, please explain.

Duane Hookom said:
Did you import/paste the module/function into your MDB file? The sample file
contains a function in a module that must be imported or pasted into your
mdb for the solution to work.

--
Duane Hookom
MS Access MVP
--

Bltony said:
Hi Duane, thanks so much for your help. However, when I ran the query, it
said "undefined function 'Concatenate' in expression.

Here is my SQL:
SELECT [my table].[Subs Name], Concatenate("SELECT [my table].[Customer
Service Rep] FROM [my table] WHERE [my table].[Subs Name] ='" & [my
table].[Subs Name] & "'") As AllCustReps
FROM [my table]
GROUP BY [my table].[Subs Name], Concatenate("SELECT [my table].[Customer
Service Rep] FROM [my table] WHERE [my table].[Subs Name] ='" & [my
table].[Subs Name] & "'");

Please review and see if I missed something. Thanks Duane.


Duane Hookom said:
There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Your expression might be
SELECT [Subs Name], Concatenate("SELECT [Customer Service Rep] FROM [as
the
followings] WHERE [Subs Name] ='" & [Subs Name] & "'") As AllCustReps
FROM [as the followings]
GROUP BY [Subs Name], Concatenate("SELECT [Customer Service Rep] FROM [as
the followings] WHERE [Subs Name] ='" & [Subs Name] & "'");

--
Duane Hookom
MS Access MVP
--

Good day everyone,

I have a table as the followings:
Subs Name Date Serv Customer Service Rep
AAA 10/25/05 Barbara
AAA 10/30/05 Shirley
AAA 11/02/05 John
BBB 9/08/05 Jack
BBB 12/1/05 John
BBB 12/10/05 Barbara

I would like to tract the names of Customer Service Rep for each
subscriber.
For the table above, I would like to have the result as:

AAA Barbara, Shirley, John
BBB Jack, John, Barbara

Please help. Many thanks.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Let me know if you have any issues following these steps. Adding code to a
application can seem quite complex at first but it isn't all that difficult
once you have a basic understanding...

The sample mdb file (download) contains a module with a function named
concatenate. To use any function like this, open a new module in your mdb.
Copy the code from "Function Con..." to "End Function" into the new module.
Select Debug|Compile to make sure there are no compile errors. Then save the
module as "modConcatenate". You can then use the Concatenate() function as
an expression in a query or control source or other places.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function


--
Duane Hookom
MS Access MVP
--

Bltony said:
Hi Duane,
I am sorry I don't have a lot of experiences in Access, so would you
please
walk with me step by step to explain what do you mean by import or paste
into
my MDB file.

I have a table as first posted and I did a SQL query as I just posted. Now
what do I need to do? Please, please explain.

Duane Hookom said:
Did you import/paste the module/function into your MDB file? The sample
file
contains a function in a module that must be imported or pasted into your
mdb for the solution to work.

--
Duane Hookom
MS Access MVP
--

Bltony said:
Hi Duane, thanks so much for your help. However, when I ran the query,
it
said "undefined function 'Concatenate' in expression.

Here is my SQL:
SELECT [my table].[Subs Name], Concatenate("SELECT [my table].[Customer
Service Rep] FROM [my table] WHERE [my table].[Subs Name] ='" & [my
table].[Subs Name] & "'") As AllCustReps
FROM [my table]
GROUP BY [my table].[Subs Name], Concatenate("SELECT [my
table].[Customer
Service Rep] FROM [my table] WHERE [my table].[Subs Name] ='" & [my
table].[Subs Name] & "'");

Please review and see if I missed something. Thanks Duane.


:

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Your expression might be
SELECT [Subs Name], Concatenate("SELECT [Customer Service Rep] FROM
[as
the
followings] WHERE [Subs Name] ='" & [Subs Name] & "'") As AllCustReps
FROM [as the followings]
GROUP BY [Subs Name], Concatenate("SELECT [Customer Service Rep] FROM
[as
the followings] WHERE [Subs Name] ='" & [Subs Name] & "'");

--
Duane Hookom
MS Access MVP
--

Good day everyone,

I have a table as the followings:
Subs Name Date Serv Customer Service Rep
AAA 10/25/05 Barbara
AAA 10/30/05 Shirley
AAA 11/02/05 John
BBB 9/08/05 Jack
BBB 12/1/05 John
BBB 12/10/05 Barbara

I would like to tract the names of Customer Service Rep for each
subscriber.
For the table above, I would like to have the result as:

AAA Barbara, Shirley, John
BBB Jack, John, Barbara

Please help. Many thanks.
Click to expand...
Click to expand...
Click to expand...
 
Top