Combining rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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] & "'");
 
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...
 
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...
 
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...
 
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...
 
Hi,Duane
Your answer benefits me much and resolved my similar problem,and i have
another problem is that my query can run correctly in Access, but if i open
it in ASP,then a reporting error named "Undefined function 'Concatenate' in
expression", so could you give me kind help again on how to resolve it?
 
The Concatenate function will not be available in an ASP page. You would
need to handle your concatenation with vbscript or other method.
 
Hi, Duane,

I consider to accomplish it just by query other than a Function,but i almost
have no any clue to do this,my expected result is:
Original Table:
Date Model
2006/1/1 A
2006/1/1 B
2006/2/1 C
2006/2/1 D
Results:
2006/1/1 A;B
2006/2/1 C;D
So could you give me help again if possible?
 
If your question still refers to an ASP page then you need to ask your
question in an ASP news group/forum.
 
Hi, Duane,

I worked out the results like: Query2
Dates 1 2 3
2006/1/1 A B C
2006/2/1 A B
and i use another query to do my expected results,
Query Code is: Query3

SELECT Query2.Dates, [1] & IIf(IsNull([2]),"",";" & [2]) &
IIf(IsNull([3]),"",";" & [3]) AS Model
FROM Query2;

and result is:
Dates Model
2006/1/1 A;B;C
2006/2/1 A;B

But the Query2's Column is Varying, not always 1,2,3, so how could i quote
the column with a variant other than a Field Name,because error will occurs
if the field name changed,
 
I would attempt do the concatenation in your ASP page where you want to
display the results.

--
Duane Hookom
MS Access MVP
--

Xuehong said:
Hi, Duane,

I worked out the results like: Query2
Dates 1 2 3
2006/1/1 A B C
2006/2/1 A B
and i use another query to do my expected results,
Query Code is: Query3

SELECT Query2.Dates, [1] & IIf(IsNull([2]),"",";" & [2]) &
IIf(IsNull([3]),"",";" & [3]) AS Model
FROM Query2;

and result is:
Dates Model
2006/1/1 A;B;C
2006/2/1 A;B

But the Query2's Column is Varying, not always 1,2,3, so how could i quote
the column with a variant other than a Field Name,because error will
occurs
if the field name changed,
--
Best Regards
Xuehong


Duane Hookom said:
If your question still refers to an ASP page then you need to ask your
question in an ASP news group/forum.
Click to expand...
Click to expand...
 
Oh,I feel so appreciation no matter it can accomplish it or not,i am so sorry
to bring you the troubles to have to cover ASP which is not your subject,
--
Best Regards
Xuehong


Duane Hookom said:
I would attempt do the concatenation in your ASP page where you want to
display the results.

--
Duane Hookom
MS Access MVP
--

Xuehong said:
Hi, Duane,

I worked out the results like: Query2
Dates 1 2 3
2006/1/1 A B C
2006/2/1 A B
and i use another query to do my expected results,
Query Code is: Query3

SELECT Query2.Dates, [1] & IIf(IsNull([2]),"",";" & [2]) &
IIf(IsNull([3]),"",";" & [3]) AS Model
FROM Query2;

and result is:
Dates Model
2006/1/1 A;B;C
2006/2/1 A;B

But the Query2's Column is Varying, not always 1,2,3, so how could i quote
the column with a variant other than a Field Name,because error will
occurs
if the field name changed,
--
Best Regards
Xuehong


Duane Hookom said:
If your question still refers to an ASP page then you need to ask your
question in an ASP news group/forum.

--
Duane Hookom
MS Access MVP
--

Hi, Duane,

I consider to accomplish it just by query other than a Function,but i
almost
have no any clue to do this,my expected result is:
Original Table:
Date Model
2006/1/1 A
2006/1/1 B
2006/2/1 C
2006/2/1 D
Results:
2006/1/1 A;B
2006/2/1 C;D
So could you give me help again if possible?
--
Thank you so much
Xuehong


:

The Concatenate function will not be available in an ASP page. You
would
need to handle your concatenation with vbscript or other method.

--
Duane Hookom
MS Access MVP
--

message
Hi,Duane
Your answer benefits me much and resolved my similar problem,and i
have
another problem is that my query can run correctly in Access, but if
i
open
it in ASP,then a reporting error named "Undefined function
'Concatenate'
in
expression", so could you give me kind help again on how to resolve
it?
--
Best Regards
Xuehong


:

Congrats...

--
Duane Hookom
MS Access MVP
--

Wow! It works! You are life saver. Thanks a million.

:

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subquery with subraction - challenging 4
Any way to shift data axes? 2
Union query 8
calculating value in a query 1
Help! Calculate Average using Queries 2
SQL query to select rows with highest vales 2
Filter between Dates 2
Formula require for complex lookup 4

Back
Top