Concatenate rows in the same table using faq701-4233 2

[jjk238]

I have looked all over, and there is a ton of info on this, but I can't seem to get anything to work. I am trying to use this FAQ:

http://www.tek-tips.com/faqs.cfm?fid=4233

I have one table named Zip_Vert:

State Affiliate County Zip
PA PA-3 Allegheny 15227
PA PA-3 Allegheny 15229
PA PA-1 Washington 15336
PA PA-1 Washington 15444
PA PA-1 Washington 15222

I would like to make this file:

State Affiliate County Zip
PA PA-3 Allegheny 15227, 15229
PA PA-1 Washington 15336, 15444, 15222

It can be in a new table or in the same one. I created the module, and then called the function using a button, passing a SQL statement to it. I think that may be where I'm messing up. I have tried multiple SQL statements with no luck. How does it know what to concatenate on? Does it just have to be a basic select statement or something else?

Any help would be greatly appreciated!!!

 

[traingamer]

It's concatenating the field you want to concatenate. You have to have a primary key to use it as written - I'm guessing that 'affiliate' is the key you're grouping on so something like:

Select State, Affiliate, County, 
Concatenate("SELECT Zip FROM Zip_Vert
    '     WHERE Affiliate =""" & [Affiliate] & """") as Zips
From Zip_Vert

will return a concatenated list of zips.

(Don't forget Allegheny 15090 while you're at it. This may, of course, give you a really long string for counties with lots of zip codes.)


Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[jjk238]

I'm not sure what I'm doing wrong here, but I keep getting different error's. I added a primary key that is just your basic Access key, named ID. I didn't have a primary key before besides the Zip codes. ID is a number. Here is what I am passing it:

strSQL = "SELECT State, Affiliate, County Concatenate(SELECT Zip FROM Zip_Vert WHERE [ID] =" & "[ID]" & ") as Zips FROM Zip_Vert"

I have tried many different combinations, but it either errs there or during the execution of the module. In this case it gives me a syntax error (missing operator). Here is the code that I have for the module:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String

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

It errors when it tries to open the query, so that is probably the issue. Any ideas?

 

[PHV]

strSQL = "SELECT DISTINCT State, Affiliate, County, Concatenate(""SELECT Zip" _
  & " FROM Zip_Vert WHERE State='"" & [State] & ""' AND Affiliate='"" & [Affiliate]" _
  & " & ""' AND County='"" & [County] & ""'"") AS Zips FROM Zip_Vert"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886