Build comma-delimited list from table

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?

re: Build comma-delimited list from table

Per argniw@yahoo.com:[color=blue]
>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.[/color]

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

re: Build comma-delimited list from table

"(PeteCresswell)" <x@y.z.invalid.USA> wrote in
news:u2pjh111eggkohnct4j0n3dktulelh38o1@4ax.com:
[color=blue]
> Per argniw@yahoo.com:[color=green]
>>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.[/color]
>
> 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)[/color]

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.

re: Build comma-delimited list from table

argniw@yahoo.com wrote:[color=blue]
> 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?[/color]


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(Num 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.OpenRecordset(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

re: Build comma-delimited list from table

Per Bob Quintal:[color=blue]
>That's exactly how Dev Ashish wrote the fConcatChild function
>http://www.mvps.org/access/modules/mdl0004.htm
>
>Why reinvent the wheel?[/color]

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

re: Build comma-delimited list from table

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

osmethod

re: Build comma-delimited list from table

"(PeteCresswell)" <x@y.z.invalid.USA> wrote in
news:ossjh1lg8j2f43f5l5qsng2a4c32t55dsl@4ax.com:
[color=blue]
> Per Bob Quintal:[color=green]
>>That's exactly how Dev Ashish wrote the fConcatChild function
>>http://www.mvps.org/access/modules/mdl0004.htm
>>
>>Why reinvent the wheel?[/color]
>
> 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.[/color]

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.

re: Build comma-delimited list from table

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:[color=blue]
> "(PeteCresswell)" <x@y.z.invalid.USA> wrote in
> news:ossjh1lg8j2f43f5l5qsng2a4c32t55dsl@4ax.com:
>[color=green]
> > Per Bob Quintal:[color=darkred]
> >>That's exactly how Dev Ashish wrote the fConcatChild function
> >>http://www.mvps.org/access/modules/mdl0004.htm
> >>
> >>Why reinvent the wheel?[/color]
> >
> > 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.[/color]
>
> 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.[/color]

re: Build comma-delimited list from table

"(PeteCresswell)" <x@y.z.invalid.USA> wrote in
news:u2pjh111eggkohnct4j0n3dktulelh38o1@4ax.com:
[color=blue]
> Per argniw@yahoo.com:[color=green]
>>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.[/color]
>
> 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)[/color]

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

re: Build comma-delimited list from table

osmethod@eircom.net wrote in
news:1125763718.048069.267820@f14g2000cwb.googlegr oups.com:
[color=blue]
> You should also set "Unique Values" to yes in the query.....[/color]

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

re: Build comma-delimited list from table

argniw@yahoo.com wrote in
news:1125772366.094548.84090@z14g2000cwz.googlegro ups.com:
[color=blue]
> 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.[/color]

build a query to select the distinct persons and the function in
the second column.

Bob Quintal wrote:[color=blue][color=green]
>> "(PeteCresswell)" <x@y.z.invalid.USA> wrote in
>> news:ossjh1lg8j2f43f5l5qsng2a4c32t55dsl@4ax.com:
>>[color=darkred]
>> > 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.[/color]
>>
>> 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.[/color]
>[/color]



--
Bob Quintal

PA is y I've altered my email address.

re: Build comma-delimited list from table

good point...


David W. Fenton wrote:[color=blue]
> osmethod@eircom.net wrote in
> news:1125763718.048069.267820@f14g2000cwb.googlegr oups.com:
>[color=green]
> > You should also set "Unique Values" to yes in the query.....[/color]
>
> 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[/color]

re: Build comma-delimited list from table

I set up the tablet and function in a module, but the module does not
seem to be doing anything.

re: Build comma-delimited list from table

argniw@yahoo.com wrote:[color=blue]
> I set up the tablet and function in a module, but the module does not
> seem to be doing anything.[/color]

Have you called it anywhere? Code in modules doesn'tjust execute on its own.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: Build comma-delimited list from table

argniw@yahoo.com wrote:[color=blue]
> I set up the tablet and function in a module, but the module does not
> seem to be doing anything.[/color]

Sorry, but did you try my post earlier. I setup a table as you showed
and tested it... it shows exactly what you requested to see...?

osmethod

re: Build comma-delimited list from table

I created a query and added the residentID column and a new column as
you suggested, calling the function in the Forests:
ConcatDetail([ResidentID]) column in the query. Do I need to do
anything else?

osmet...@eircom.net wrote:[color=blue]
> argniw@yahoo.com wrote:[color=green]
> > I set up the tablet and function in a module, but the module does not
> > seem to be doing anything.[/color]
>
> Sorry, but did you try my post earlier. I setup a table as you showed
> and tested it... it shows exactly what you requested to see...?
>
> osmethod[/color]

re: Build comma-delimited list from table

argniw@yahoo.com wrote:[color=blue]
> I created a query and added the residentID column and a new column as
> you suggested, calling the function in the Forests:
> ConcatDetail([ResidentID]) column in the query. Do I need to do
> anything else?
>
> osmet...@eircom.net wrote:[color=green]
> > argniw@yahoo.com wrote:[color=darkred]
> > > I set up the tablet and function in a module, but the module does not
> > > seem to be doing anything.[/color]
> >
> > Sorry, but did you try my post earlier. I setup a table as you showed
> > and tested it... it shows exactly what you requested to see...?
> >
> > osmethod[/color][/color]

Step 1
Add "ResidentID" field to your many table and your table should now
look 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


Step 2
Create a separate new vba module and put the following vba function
into this module,
(Copy the following code and paste it into your module)

'---Copy from Here
Function ConcatDetail(Num 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.OpenRecordset(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
'---To Here

Step 3
Create a new query on the table (your many table (Step 1) above and
called "Table1" in the code in (Step 2). (Change the name of the table
in the code to the actual name of your table).

Add the "Resident" field to the Query and create
another new field beside this e.g. Put the Following in a "Field" in
the query...

Forests: ConcatDetail([ResidentID])

With the query, set "Unique Values" to yes.

Step 4
Run this query and you should see what you require.


osmethod

re: Build comma-delimited list from table

On this line

Dim MyDB As Database

I am getting an error stating: User-defined type not defined.

Is there a problem with the "As Database" portion?

re: Build comma-delimited list from table

What version of Access? Database is a DAO object. By default, Access 2000
and Access 2002 both use ADO, with no reference preset to DAO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



<argniw@yahoo.com> wrote in message
news:1125951689.002076.194730@g14g2000cwa.googlegr oups.com...[color=blue]
> On this line
>
> Dim MyDB As Database
>
> I am getting an error stating: User-defined type not defined.
>
> Is there a problem with the "As Database" portion?
>[/color]

re: Build comma-delimited list from table

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
news:tbGdnQiwC4jsJYHeRVn-sA@rogers.com:
[color=blue]
> With any code module open, select Tools | References from the menu
> bar, scroll through the list of available references until you
> find the one for Microsoft DAO 3.6 Object Library, and select it.
> If you're not going to be using ADO, uncheck the reference to
> Microsoft ActiveX Data Objects 2.1 Library
>
> If you have both references, you'll find that you'll need to
> "disambiguate" certain declarations, because objects with the same
> names exist in the 2 models. For example, to ensure that you get a
> DAO recordset, you'll need to use Dim rsCurr as DAO.Recordset (to
> guarantee an ADO recordset, you'd use Dim rsCurr As
> ADODB.Recordset)[/color]

I think it's good practice to disambiguate regardless of which
library you're using.

But the default A2K mdb stupidly doesn't have a DAO reference at
all.

So, my advice to the original poster is, first, disambiguate by
making it Dim db As DAO.Database and if that gives an error, *then*
go to the references and add the DAO reference (and test whether you
can get away with completely removing the ADO reference, which is
useless for most Jet-only databases).

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

re: Build comma-delimited list from table

David W. Fenton wrote:
[color=blue]
> I think it's good practice to disambiguate regardless of which
> library you're using.[/color]

How do you do this with variable representation?

For instance, collections are usually

dim frm as Access.Form, etc,

recordsets, etc

dim Dbs as DAO.Database

dim rst as ADODB.Recordset

dim rst as DAO.recordset

and so on.

But what about:

Dim strSilly as String
dim intI as Integer

etc?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Build comma-delimited list from table

Tim Marshall <TIMMY!@PurplePandaChasers.Moertherium> wrote in
news:dfng9h$uvd$1@coranto.ucs.mun.ca:
[color=blue]
> David W. Fenton wrote:
>[color=green]
>> I think it's good practice to disambiguate regardless of which
>> library you're using.[/color]
>
> How do you do this with variable representation?[/color]

Eh? What do you mean?

Dim db As DAO.Database

is pretty easy.
[color=blue]
> For instance, collections are usually
>
> dim frm as Access.Form, etc,[/color]

That doesn't need to be disambiguated, since there's only one
possible meaning for it, and because it's one that is *never* going
to be missing.
[color=blue]
> recordsets, etc
>
> dim Dbs as DAO.Database
>
> dim rst as ADODB.Recordset
>
> dim rst as DAO.recordset[/color]

It's only ADO and DAO that need to be disambiguated.
[color=blue]
> and so on.
>
> But what about:
>
> Dim strSilly as String
> dim intI as Integer
>
> etc?[/color]

There is no possible library conflict there, since those are native
VBA data types, so there's nothing ambiguous about them, so no need
to qualify them.

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

re: Build comma-delimited list from table

David W. Fenton wrote:
[color=blue]
> There is no possible library conflict there, since those are native
> VBA data types, so there's nothing ambiguous about them, so no need
> to qualify them.[/color]

Thanks. After reading Michka's article years ago, I went into a frenzy
of making sure everything I had was disambiuated, some of which, as
you've observed, unnecessarily. I just do it out of habit now and still
wonder from time to time about the VBA data types. 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me