concatenate?

  • Thread starter Thread starter Renee Moffett
  • Start date Start date
R

Renee Moffett

In tblServicingZips there are 2 fields: CustNum and Zip.

I need to be able to report it like this. CustNum 100: 40202, 40203, 40204

Can anyone help me find information on how to do this? I've looked on the
MS website, but since I don't know what to call this, I'm probably not using
the right term. Thanks.
 
Duane Thanks. I'm having trouble applying it to my data. You have 2
tables, whereas I have only one. Do I need to create a new table to relate
a number to CustNum? It would seem you might have been able to use only
tblFamMem and use famLastName in the place of FamID. Since you didn't I'm
guessing 2 tables are required to do this?

If I can post anything that makes it easier to understand where I am, let me
know. I'm using DAO and I think I commented/uncommented things the way they
should be.

Thanks again.

Renee
 
You can do this all with one table. One possibility is to create a
totals/group by to get the "one side" unique values. Base your query with
concatenate on the totals query.
 
Drat, Duane! I'm still having problems with it. I'm posting the SQL from
both queries and the code as it stands now and hope you can help me find my
error. Here are the references I have checked, in case that makes
difference: Visual Basic for Applications, MS Access 10.0 Object Library, MS
DAO 3.6 Object Library, OLE Automation, Microsoft Visual Basic for
Applications Extensibility 5.3.

The query to get unique values is 'qryMiniMktPlanConcatBasis'. Here's its
SQL.

SELECT DISTINCT tblFHSvcZips.FusionCustNum
FROM tblFHSvcZips;

Here's the query running the code you graciously provided.

SELECT qryMiniMktPlanConcatBasis.FusionCustNum, Concatenate("SELECT
FirstName FROM tblFHSvcZips WHERE FusionCustNum=" & [FusionCustNum]) AS Zips
FROM qryMiniMktPlanConcatBasis;

Here's the module code as it now stands. I'm getting "Run time error 3061:
Too few parameters. Expected 2." on the line indicated below. Thanks so
much for your continued help and patience, Duane. Have a great
eekend. ---Renee

Option Compare Database

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'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) ' RUN TIME ERROR 3061 HERE

'======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
 
Renee,
You need to replace FirstName in the function with your field name. Also, if
FusionCustNum is text, you will need to use:
..WHERE FusionCustNum=""" & [FusionCustNum] & """")...

--
Duane Hookom
MS Access MVP


Renee Moffett .Moffett .com> said:
Drat, Duane! I'm still having problems with it. I'm posting the SQL from
both queries and the code as it stands now and hope you can help me find my
error. Here are the references I have checked, in case that makes
difference: Visual Basic for Applications, MS Access 10.0 Object Library, MS
DAO 3.6 Object Library, OLE Automation, Microsoft Visual Basic for
Applications Extensibility 5.3.

The query to get unique values is 'qryMiniMktPlanConcatBasis'. Here's its
SQL.

SELECT DISTINCT tblFHSvcZips.FusionCustNum
FROM tblFHSvcZips;

Here's the query running the code you graciously provided.

SELECT qryMiniMktPlanConcatBasis.FusionCustNum, Concatenate("SELECT
FirstName FROM tblFHSvcZips WHERE FusionCustNum=" & [FusionCustNum]) AS Zips
FROM qryMiniMktPlanConcatBasis;

Here's the module code as it now stands. I'm getting "Run time error 3061:
Too few parameters. Expected 2." on the line indicated below. Thanks so
much for your continued help and patience, Duane. Have a great
eekend. ---Renee

Option Compare Database

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'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) ' RUN TIME ERROR 3061 HERE

'======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 said:
You can do this all with one table. One possibility is to create a
totals/group by to get the "one side" unique values. Base your query with
concatenate on the totals query.

--
Duane Hookom
MS Access MVP


let way
they
Click to expand...
looked
on
Click to expand...
Click to expand...
 
"Duane Hookom" wrote
There is a sample download under my name at
http://www.rogersaccesslibrary.com/OtherLibraries.asp. Find "Generic
function to..."
Click to expand...

C'mon Duane you don't need a function for this.
I've already posted how to do this with a simple
Update statement.Don't you Access guru's ever
listen? :~)

Best,

P.S. Have you seen/used Yukon's new crosstab
stuff?They don't listen to me much either :~).
And if you have any pull ask them where's all the
sql99 analytic/windows functions that are currently
in Oracle and DB2.Is there something their water?:).

Check out utilities for S2k @
www.rac4sql.net
 
I didn't see your post in this thread. I thought you posted solutions
primarily for SQL databases. Doesn't your update statement require a new
field or table to be implemented.
 
Hallelujah. It is text, so this did it. Also, I did need to correct the
field name I was retreiving. THANK YOU!



Duane Hookom said:
Renee,
You need to replace FirstName in the function with your field name. Also, if
FusionCustNum is text, you will need to use:
..WHERE FusionCustNum=""" & [FusionCustNum] & """")...

--
Duane Hookom
MS Access MVP


Renee Moffett .Moffett .com> said:
Drat, Duane! I'm still having problems with it. I'm posting the SQL from
both queries and the code as it stands now and hope you can help me find my
error. Here are the references I have checked, in case that makes
difference: Visual Basic for Applications, MS Access 10.0 Object
Click to expand...
Library,
MS
DAO 3.6 Object Library, OLE Automation, Microsoft Visual Basic for
Applications Extensibility 5.3.

The query to get unique values is 'qryMiniMktPlanConcatBasis'. Here's its
SQL.

SELECT DISTINCT tblFHSvcZips.FusionCustNum
FROM tblFHSvcZips;

Here's the query running the code you graciously provided.

SELECT qryMiniMktPlanConcatBasis.FusionCustNum, Concatenate("SELECT
FirstName FROM tblFHSvcZips WHERE FusionCustNum=" & [FusionCustNum]) AS Zips
FROM qryMiniMktPlanConcatBasis;

Here's the module code as it now stands. I'm getting "Run time error 3061:
Too few parameters. Expected 2." on the line indicated below. Thanks so
much for your continued help and patience, Duane. Have a great
eekend. ---Renee

Option Compare Database

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'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) ' RUN TIME ERROR 3061 HERE

'======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 said:
You can do this all with one table. One possibility is to create a
totals/group by to get the "one side" unique values. Base your query with
concatenate on the totals query.

--
Duane Hookom
MS Access MVP


"Renee Moffett .Moffett .com>" <renee<nospam> wrote in message

Duane Thanks. I'm having trouble applying it to my data. You have 2
tables, whereas I have only one. Do I need to create a new table to
relate
a number to CustNum? It would seem you might have been able to use only
tblFamMem and use famLastName in the place of FamID. Since you
Click to expand...
didn't
I'm
guessing 2 tables are required to do this?

If I can post anything that makes it easier to understand where I
Click to expand...
am,
let
me
know. I'm using DAO and I think I commented/uncommented things the way
they
should be.

Thanks again.

Renee

There is a sample download under my name at
http://www.rogersaccesslibrary.com/OtherLibraries.asp. Find "Generic
function to..."

--
Duane Hookom
MS Access MVP


"Renee Moffett .Moffett .com>" <renee<nospam> wrote in message
In tblServicingZips there are 2 fields: CustNum and Zip.

I need to be able to report it like this. CustNum 100: 40202, 40203,
40204

Can anyone help me find information on how to do this? I've
Click to expand...
looked
on
the
MS website, but since I don't know what to call this, I'm
Click to expand...
probably
not
using
the right term. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top