Build comma-delimited list from table

[argniw]

I have a table that looks like this:

Forest Residents
Black Josh
Black Joellen
Black Mary Jane
Brown Gertrude
Brown Josh
Brown Mary Jane
I've seen some UDFs for SQL Server to build a comma-delimited list, but
I need a function to use entirely in Access to build the following:

Residents Forests
Josh Black, Brown
Joellen Black
Mary Jane Black, Brown
Gertrude Brown

The table above indicates the forests each person has visited in a
comma-delimited list.

Any suggestions?

[(PeteCresswell)]

Per ar****@yahoo.co m:

Residents Forests
Josh Black, Brown
Joellen Black
Mary Jane Black, Brown
Gertrude Brown

The table above indicates the forests each person has visited in a
comma-delimited list.

If I were doing it, I'd open up the source table sorted by Person/Forest and
just iterate through the table, concatenating PersonName|Tab| ForestName into a
string variable - and flushing the string to disk each time PersonName changes
(not forgetting to flush the last one at .EOF)
--
PeteCresswell

[Bob Quintal]

"(PeteCresswell )" <[e-mail]. USA> wrote in
news:u2******** *************** *********@4ax.c om:

Per ar****@yahoo.co m:

Residents Forests
Josh Black, Brown
Joellen Black
Mary Jane Black, Brown
Gertrude Brown

The table above indicates the forests each person has visited
in a comma-delimited list.

If I were doing it, I'd open up the source table sorted by
Person/Forest and just iterate through the table,
concatenating PersonName|Tab| ForestName into a string variable
- and flushing the string to disk each time PersonName changes
(not forgetting to flush the last one at .EOF)

That's exactly how Dev Ashish wrote the fConcatChild function
http://www.mvps.org/access/modules/mdl0004.htm

Why reinvent the wheel?

--
Bob Quintal

PA is y I've altered my email address.

[osmethod]

ar****@yahoo.co m wrote:

I have a table that looks like this:

Forest Residents
Black Josh
Black Joellen
Black Mary Jane
Brown Gertrude
Brown Josh
Brown Mary Jane
I've seen some UDFs for SQL Server to build a comma-delimited list, but
I need a function to use entirely in Access to build the following:

Residents Forests
Josh Black, Brown
Joellen Black
Mary Jane Black, Brown
Gertrude Brown

The table above indicates the forests each person has visited in a
comma-delimited list.

Any suggestions?

Firstly, I'm assuming their is a "ResidentID " field in your many table
above and it looks as follows:

ResidentID Forest Residents
1 Black Josh
2 Black Joellen
3 Black Mary Jane
4 Brown Gertrude
1 Brown Josh
3 Brown Mary Jane

If so, put the following function (a modified version of one by Allen
Browne) into a module:

Function ConcatDetail(Nu m As Long) As Variant
Dim MyDB As Database
Dim rst As Recordset
Dim strOut As String
Dim strSql As String
Dim lngLen As Long

Const strcSep = ","

Set MyDB = CurrentDb()

'Change Table1 to your table's name
strSql = "SELECT Forest FROM Table1 WHERE ResidentID = " & Num & ";"
Set rst = MyDB.OpenRecord set(strSql)
With rst
Do While Not .EOF
strOut = strOut & !Forest & strcSep
.MoveNext
Loop
End With
rst.Close

lngLen = Len(strOut) - Len(strcSep)
If lngLen > 0 Then
ConcatDetail = Left(strOut, lngLen)
Else
ConcatDetail = Null
End If

Set rst = Nothing
Set MyDB = Nothing
End Function
Next, Create a query on the table (Your 1st table shown above and
called Table1 in this example). Add the "Resident" field and create
another field beside this e.g.

Forests: ConcatDetail([ResidentID])

Run this query and you should see what you require.

osmethod

[(PeteCresswell)]

Per Bob Quintal:

That's exactly how Dev Ashish wrote the fConcatChild function
http://www.mvps.org/access/modules/mdl0004.htm

Why reinvent the wheel?

I must've misunderstood the OP.

I thought all the data were in a single table - with multiple records for each
person depending on how many forests.
--
PeteCresswell

[osmethod]

You should also set "Unique Values" to yes in the query.....

osmethod

[Bob Quintal]

"(PeteCresswell )" <[e-mail]. USA> wrote in
news:os******** *************** *********@4ax.c om:

Per Bob Quintal:

That's exactly how Dev Ashish wrote the fConcatChild function
http://www.mvps.org/access/modules/mdl0004.htm

Why reinvent the wheel?

I must've misunderstood the OP.

I thought all the data were in a single table - with multiple
records for each person depending on how many forests.

That's exactly what fConcatChild() expects. It goes thrugh the
table, adding the value from each record to a list, where the
filter would be based on the person field.

--
Bob Quintal

PA is y I've altered my email address.

[argniw]

Yes I am trying to implement this With the source data all in one table
..I'm not sure how to implement this given the info you provided.
Bob Quintal wrote:

"(PeteCresswell )" <[e-mail]. USA> wrote in
news:os******** *************** *********@4ax.c om:

Per Bob Quintal:

That's exactly how Dev Ashish wrote the fConcatChild function
http://www.mvps.org/access/modules/mdl0004.htm

Why reinvent the wheel?

I must've misunderstood the OP.

I thought all the data were in a single table - with multiple
records for each person depending on how many forests.

That's exactly what fConcatChild() expects. It goes thrugh the
table, adding the value from each record to a list, where the
filter would be based on the person field.

--
Bob Quintal

PA is y I've altered my email address.

[David W. Fenton]

"(PeteCresswell )" <[e-mail]. USA> wrote in
news:u2******** *************** *********@4ax.c om:

Per ar****@yahoo.co m:

Residents Forests
Josh Black, Brown
Joellen Black
Mary Jane Black, Brown
Gertrude Brown

The table above indicates the forests each person has visited in a
comma-delimited list.

If I were doing it, I'd open up the source table sorted by
Person/Forest and just iterate through the table, concatenating
PersonName|Tab| ForestName into a string variable - and flushing
the string to disk each time PersonName changes (not forgetting to
flush the last one at .EOF)

Why not just SELECT DISTINCT, then you don't have to code to check
when the name changes?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

os******@eircom .net wrote in
news:11******** **************@ f14g2000cwb.goo glegroups.com:

You should also set "Unique Values" to yes in the query.....

And you should also probably remove the ones that have Null or
zero-length string in the field you're concatenative, or you'll get
consecutive commas.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc