Multiple Rows Into One Row comma delimeted string using Access

[jmcclow]

Any wise guys out there?

I have been trying to get this to work for hours, but lack the experience to pull it off. I have provided an example with the names of my actual table and fields.

Table name: eventlog

SELECT e.period, e.center, e.event
FROM [eventlog] as e

results in this:

Period Center Event
p1 a tv
p1 a radio
p1 a web
p1 b magazine
p1 b newsad
p1 b tv
p2 a radio
p2 a web
p2 b radio
p2 b web

Desired Format:

Period Center Event
p1 a tv, radio, web
p1 b magazine, newsad, tv
p2 a radio, web
p2 b radio, web

I have very limited experience in vba, but I think that I need a function that creates a comma delimited string. I need somthing to run in ACCESS 2002.

1) Can you provide code and let me know where to put it (where in the module?)

2) How do I modify the query listed above so that it will work with the stored function?

I tried to be specific as possible, I hope this is clear to understand. I greatly appreciate any help provided.

 

[Beetle]

Check out Duane Hookoms Concatenate sample db at this link;

http://www.rogersaccesslibrary.com/forum/generic-function-to-concatenate-child-records_topic16.html

 

[jmcclow]

Thanks for providing that link "Beetle", I really appreciate your help.

It seems to be hanging up on this part of the code:
'Dim rs As DAO.Recordset

and then it highlights in yellow:
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String


I am trying to summarize event_date and then list the events seperated by commas.

The data listed as:
Radio Ad East
Radio Ad West
Radio ad North
Radio Ad South

Would be
Radio Ad East, West, North , South

My query is:
SELECT me.event_date, Concatenate("SELECT me.event FROM [marketing_events] as me WHERE event =" & [event]) AS EventRollup
FROM marketing_events AS me;

I then copy/pasted the module:

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)
'======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



I tried to run this in Access 2007, is that possible?