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.com:

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]> wrote in
news:u2********************************@4ax.com:

Per ar****@yahoo.com:

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.com 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(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

[(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]> wrote in
news:os********************************@4ax.com:

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]> wrote in
news:os********************************@4ax.com:

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]> wrote in
news:u2********************************@4ax.com:

Per ar****@yahoo.com:

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.googlegr oups.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

[Bob Quintal]

ar****@yahoo.com wrote in
news:11*********************@z14g2000cwz.googlegro ups.com:

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.

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

Bob Quintal wrote:

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

> 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.

--
Bob Quintal

PA is y I've altered my email address.

[osmethod]

good point...
David W. Fenton wrote:

os******@eircom.net wrote in
news:11**********************@f14g2000cwb.googlegr oups.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

[argniw]

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

[Rick Brandt]

ar****@yahoo.com wrote:

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

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

[osmethod]

ar****@yahoo.com wrote:

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

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

[argniw]

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?

[e-mail] wrote:

ar****@yahoo.com wrote:

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

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

[osmethod]

ar****@yahoo.com wrote:

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?

[e-mail] wrote:

ar****@yahoo.com wrote:

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

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

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

[argniw]

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?

[Douglas J. Steele]

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!)

<ar****@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

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?

[David W. Fenton]

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
news:tb********************@rogers.com:

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)

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

[Tim Marshall]

David W. Fenton wrote:

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

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

[David W. Fenton]

Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:df**********@coranto.ucs.mun.ca:

David W. Fenton wrote:

I think it's good practice to disambiguate regardless of which
library you're using.
How do you do this with variable representation?

Eh? What do you mean?

Dim db As DAO.Database

is pretty easy.
For instance, collections are usually

dim frm as Access.Form, etc,
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.
recordsets, etc

dim Dbs as DAO.Database

dim rst as ADODB.Recordset

dim rst as DAO.recordset
It's only ADO and DAO that need to be disambiguated.
and so on.

But what about:

Dim strSilly as String
dim intI as Integer

etc?

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

[Tim Marshall]

David W. Fenton wrote:

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.

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