Concatenate Query

  • Thread starter Thread starter Dean Fraiquin
  • Start date Start date
D

Dean Fraiquin

Hi,

I have a database with Company info, and a Products table
that is joined by unique client ID.

A new table is created which has one record for each
product listing the unique client ID.

I want to create a query whereby lt looks at the client
ID and creates a single field of all products seperated
by a comma and space. Now I've look around the web, and
it seems possible but I do not really understand the SQL
stuff. The query currently looks like this:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service]
FROM Services;

What additional text do I need to input to make my dreams
come true???

Many thanks for any help.

Dean Fraiquin
 
You can use a generic function. Copy the below code into a new module and
save it as "basConcatenate". Then, you can use it in a query as noted in the
code.

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
 
Show me your SQL of the query.

--
Duane Hookom
MS Access MVP


Dean Fraiquin said:
Duane,

Thanks for the code - I have copied it into a module and
named it and placed it in the query - however when I try
to run it it asks for Paramenter values of the pstrSQL
and the pstrdelim - i'm quite new at this so I have no
idea what to do - further advice would be greatly
appreciated!

Many apologies for not getting it, but I am trying.

Many thanks,

Dean
-----Original Message-----
You can use a generic function. Copy the below code into a new module and
save it as "basConcatenate". Then, you can use it in a query as noted in the
code.

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


Hi,

I have a database with Company info, and a Products table
that is joined by unique client ID.

A new table is created which has one record for each
product listing the unique client ID.

I want to create a query whereby lt looks at the client
ID and creates a single field of all products seperated
by a comma and space. Now I've look around the web, and
it seems possible but I do not really understand the SQL
stuff. The query currently looks like this:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service]
FROM Services;

What additional text do I need to input to make my dreams
come true???

Many thanks for any help.

Dean Fraiquin
Click to expand...


.
Click to expand...
Click to expand...
 
Duane,

Here is the SQL as it stands:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service], Concatenate([«pstrSQL»],[«pstrDelim»])
AS Expr1
FROM Services;

All help is appreciated!

Regards,

Dean
-----Original Message-----
Show me your SQL of the query.

--
Duane Hookom
MS Access MVP


Duane,

Thanks for the code - I have copied it into a module and
named it and placed it in the query - however when I try
to run it it asks for Paramenter values of the pstrSQL
and the pstrdelim - i'm quite new at this so I have no
idea what to do - further advice would be greatly
appreciated!

Many apologies for not getting it, but I am trying.

Many thanks,

Dean
-----Original Message-----
You can use a generic function. Copy the below code
Click to expand...
into
a new module and
save it as "basConcatenate". Then, you can use it in a query as noted in the
code.

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


Hi,

I have a database with Company info, and a Products table
that is joined by unique client ID.

A new table is created which has one record for each
product listing the unique client ID.

I want to create a query whereby lt looks at the client
ID and creates a single field of all products seperated
by a comma and space. Now I've look around the web, and
it seems possible but I do not really understand the SQL
stuff. The query currently looks like this:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service]
FROM Services;

What additional text do I need to input to make my dreams
come true???

Many thanks for any help.

Dean Fraiquin


.
Click to expand...
Click to expand...


.
Click to expand...
 
You must provide a parameters like:
Concatenate("SELECT ....")
You mention company and products tables but all I see is one table named
services. Your final query would have only a source table of company.
Select tblCompany.ClientID, Concatenate("Select [Product/Service] From
tblProducts p where P.ClientID = tblCompany.ClientID) as Products
From tblCompany;

--
Duane Hookom
MS Access MVP


Duane,

Here is the SQL as it stands:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service], Concatenate([«pstrSQL»],[«pstrDelim»])
AS Expr1
FROM Services;

All help is appreciated!

Regards,

Dean
-----Original Message-----
Show me your SQL of the query.

--
Duane Hookom
MS Access MVP


Duane,

Thanks for the code - I have copied it into a module and
named it and placed it in the query - however when I try
to run it it asks for Paramenter values of the pstrSQL
and the pstrdelim - i'm quite new at this so I have no
idea what to do - further advice would be greatly
appreciated!

Many apologies for not getting it, but I am trying.

Many thanks,

Dean
-----Original Message-----
You can use a generic function. Copy the below code
Click to expand...
into
a new module and
save it as "basConcatenate". Then, you can use it in a query as noted in the
code.

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


Hi,

I have a database with Company info, and a Products table
that is joined by unique client ID.

A new table is created which has one record for each
product listing the unique client ID.

I want to create a query whereby lt looks at the client
ID and creates a single field of all products seperated
by a comma and space. Now I've look around the web, and
it seems possible but I do not really understand the SQL
stuff. The query currently looks like this:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service]
FROM Services;

What additional text do I need to input to make my dreams
come true???

Many thanks for any help.

Dean Fraiquin


.
Click to expand...
Click to expand...


.
Click to expand...
 
You need to provide a legitimate SQL string in the function. The FROM clause
expects a table or query name.

SELECT id, Concatenate("SELECT [Product/Service] From [Services] WHERE
[Client ID] = " & id) as Products From [Main Data];

BTW: you would be much better off in the future if you don't use punctuation
and spaces in object names. You should find a comfortable naming convention
and stick with it.

--
Duane Hookom
MS Access MVP


Duane,

Thanks for the example - I have amended the SQL as
follows:

SELECT [Main Data].id, Concatenate("SELECT
Product/Service From [Services].Product/Service WHERE
[Services].Client ID = [Main Data].id") as Products
From [Main Data];

However, when I try to run it, I get the following
statement: Run-time error '-2147217900 (80040e14)'
Syntax error in FROM clause

And when I hit debug - the following is highlighted:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Apologies for keeping on, but I'm not that experienced
with SQL and VBA etc.

Many thanks,

Dean


-----Original Message-----
You must provide a parameters like:
Concatenate("SELECT ....")
You mention company and products tables but all I see is one table named
services. Your final query would have only a source table of company.
Select tblCompany.ClientID, Concatenate("Select [Product/Service] From
tblProducts p where P.ClientID = tblCompany.ClientID) as Products
From tblCompany;

--
Duane Hookom
MS Access MVP


Duane,

Here is the SQL as it stands:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service], Concatenate([«pstrSQL»],[«pstrDelim»])
AS Expr1
FROM Services;

All help is appreciated!

Regards,

Dean
-----Original Message-----
Show me your SQL of the query.

--
Duane Hookom
MS Access MVP


Duane,

Thanks for the code - I have copied it into a module and
named it and placed it in the query - however when I try
to run it it asks for Paramenter values of the pstrSQL
and the pstrdelim - i'm quite new at this so I have no
idea what to do - further advice would be greatly
appreciated!

Many apologies for not getting it, but I am trying.

Many thanks,

Dean
-----Original Message-----
You can use a generic function. Copy the below code into
a new module and
save it as "basConcatenate". Then, you can use it in a
query as noted in the
code.

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


message
Hi,

I have a database with Company info, and a Products
table
that is joined by unique client ID.

A new table is created which has one record for each
product listing the unique client ID.

I want to create a query whereby lt looks at the client
ID and creates a single field of all products seperated
by a comma and space. Now I've look around the web, and
it seems possible but I do not really understand the
SQL
stuff. The query currently looks like this:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service]
FROM Services;

What additional text do I need to input to make my
dreams
come true???

Many thanks for any help.

Dean Fraiquin


.
Click to expand...


.
Click to expand...


.
Click to expand...
 
Hi Dean and Duane,

I too have been frustrated by similar concatenation problems.

As an alternative to Duane's solution I'm posting another function.

The following function code has served me well although I cannot claim
to have generated it myself--its from another forum from some time back.

'************ Code Start **********
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild("Order Details", "OrderID", "Quantity", _
"Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" &
strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" &
varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & "; "
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 2)

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function
 
Duane,

I just can't thank you enough - I really appreciate your
patience and all your help.

At last it does what I want it to do!!

Once again thank you so much.

All the best,

Dean
-----Original Message-----
You need to provide a legitimate SQL string in the function. The FROM clause
expects a table or query name.

SELECT id, Concatenate("SELECT [Product/Service] From [Services] WHERE
[Client ID] = " & id) as Products From [Main Data];

BTW: you would be much better off in the future if you don't use punctuation
and spaces in object names. You should find a comfortable naming convention
and stick with it.

--
Duane Hookom
MS Access MVP


Duane,

Thanks for the example - I have amended the SQL as
follows:

SELECT [Main Data].id, Concatenate("SELECT
Product/Service From [Services].Product/Service WHERE
[Services].Client ID = [Main Data].id") as Products
From [Main Data];

However, when I try to run it, I get the following
statement: Run-time error '-2147217900 (80040e14)'
Syntax error in FROM clause

And when I hit debug - the following is highlighted:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Apologies for keeping on, but I'm not that experienced
with SQL and VBA etc.

Many thanks,

Dean


-----Original Message-----
You must provide a parameters like:
Concatenate("SELECT ....")
You mention company and products tables but all I see is one table named
services. Your final query would have only a source table of company.
Select tblCompany.ClientID, Concatenate("Select [Product/Service] From
tblProducts p where P.ClientID = tblCompany.ClientID) as Products
From tblCompany;

--
Duane Hookom
MS Access MVP


Duane,

Here is the SQL as it stands:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service], Concatenate([«pstrSQL»], [«pstrDelim»])
AS Expr1
FROM Services;

All help is appreciated!

Regards,

Dean
-----Original Message-----
Show me your SQL of the query.

--
Duane Hookom
MS Access MVP


Duane,

Thanks for the code - I have copied it into a module and
named it and placed it in the query - however when I try
to run it it asks for Paramenter values of the pstrSQL
and the pstrdelim - i'm quite new at this so I have no
idea what to do - further advice would be greatly
appreciated!

Many apologies for not getting it, but I am trying.

Many thanks,

Dean
-----Original Message-----
You can use a generic function. Copy the below code into
a new module and
save it as "basConcatenate". Then, you can use it in a
query as noted in the
code.

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


message
Hi,

I have a database with Company info, and a Products
table
that is joined by unique client ID.

A new table is created which has one record for each
product listing the unique client ID.

I want to create a query whereby lt looks at the client
ID and creates a single field of all products seperated
by a comma and space. Now I've look around the
Click to expand...
web,
and
it seems possible but I do not really understand the
SQL
stuff. The query currently looks like this:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service]
FROM Services;

What additional text do I need to input to make my
dreams
come true???

Many thanks for any help.

Dean Fraiquin


.



.
Click to expand...


.
Click to expand...


.
Click to expand...
 
Glad to see it's working for you.

--
Duane Hookom
MS Access MVP


Duane,

I just can't thank you enough - I really appreciate your
patience and all your help.

At last it does what I want it to do!!

Once again thank you so much.

All the best,

Dean
-----Original Message-----
You need to provide a legitimate SQL string in the function. The FROM clause
expects a table or query name.

SELECT id, Concatenate("SELECT [Product/Service] From [Services] WHERE
[Client ID] = " & id) as Products From [Main Data];

BTW: you would be much better off in the future if you don't use punctuation
and spaces in object names. You should find a comfortable naming convention
and stick with it.

--
Duane Hookom
MS Access MVP


Duane,

Thanks for the example - I have amended the SQL as
follows:

SELECT [Main Data].id, Concatenate("SELECT
Product/Service From [Services].Product/Service WHERE
[Services].Client ID = [Main Data].id") as Products
From [Main Data];

However, when I try to run it, I get the following
statement: Run-time error '-2147217900 (80040e14)'
Syntax error in FROM clause

And when I hit debug - the following is highlighted:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Apologies for keeping on, but I'm not that experienced
with SQL and VBA etc.

Many thanks,

Dean


-----Original Message-----
You must provide a parameters like:
Concatenate("SELECT ....")
You mention company and products tables but all I see is one table named
services. Your final query would have only a source table of company.
Select tblCompany.ClientID, Concatenate("Select [Product/Service] From
tblProducts p where P.ClientID = tblCompany.ClientID) as Products
From tblCompany;

--
Duane Hookom
MS Access MVP


Duane,

Here is the SQL as it stands:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service], Concatenate([«pstrSQL»], [«pstrDelim»])
AS Expr1
FROM Services;

All help is appreciated!

Regards,

Dean
-----Original Message-----
Show me your SQL of the query.

--
Duane Hookom
MS Access MVP


Duane,

Thanks for the code - I have copied it into a module and
named it and placed it in the query - however when I try
to run it it asks for Paramenter values of the pstrSQL
and the pstrdelim - i'm quite new at this so I have no
idea what to do - further advice would be greatly
appreciated!

Many apologies for not getting it, but I am trying.

Many thanks,

Dean
-----Original Message-----
You can use a generic function. Copy the below code into
a new module and
save it as "basConcatenate". Then, you can use it in a
query as noted in the
code.

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


message
Hi,

I have a database with Company info, and a Products
table
that is joined by unique client ID.

A new table is created which has one record for each
product listing the unique client ID.

I want to create a query whereby lt looks at the client
ID and creates a single field of all products seperated
by a comma and space. Now I've look around the
Click to expand...
web,
and
it seems possible but I do not really understand the
SQL
stuff. The query currently looks like this:

SELECT Services.ID, Services.[Client ID], Services.
[Product/Service]
FROM Services;

What additional text do I need to input to make my
dreams
come true???

Many thanks for any help.

Dean Fraiquin


.



.
Click to expand...


.
Click to expand...


.
Click to expand...
 
The functions are very similar in their functionality. fConcatChild()
doesn't allow you to set the delimiter without changing the code. My
function will also allow you to return multiple fields to get first and last
names fields returned as one and the results can be sorted.
 
Back
Top