rake-them-all-into-a-string query: is that possible?

[wnstnsmith]

Dear all,

Suppose there is a table whose records contain a textfield TXT, and a query
that returns a subset of that table. Is it possible to have that query
return all the TXT-fields gathered together in one string? I really have no
idea as to how to tackle this. For clarity, here's an example of what I
mean:

there are three tables

sports guys teams
id name id name id idsport idguy
1 volleybal 1 Tom 1 1 2
2 basketball 2 Dick 2 1 3
3 cricket 3 Harry 3 2 1
.... ... ... ... ... ... ...

Now the query should run something like: select all members of the
volleybalteam ( teams.idguy where idsport=1, and that joined on guys, etc),
and return that in the following single record:

sportsname team
volleybal Tom, Dick

The reason I ask is that I need that record for further processing
elsewhere, so solutions involving forms in any way are no use.

Thanks in advance,

WS

[Steve Jorgensen]

It's a common need, and it's not possible in a SQL query. You'll have to
write something in VB to concatenate the strings together, or find a way to
eliminate the need to deal with them in concatenated form.

On Tue, 13 Jan 2004 00:20:41 GMT, wn********@yahoo.com wrote:

Dear all,

Suppose there is a table whose records contain a textfield TXT, and a query
that returns a subset of that table. Is it possible to have that query
return all the TXT-fields gathered together in one string? I really have no
idea as to how to tackle this. For clarity, here's an example of what I
mean:

there are three tables

sports guys teams
id name id name id idsport idguy
1 volleybal 1 Tom 1 1 2
2 basketball 2 Dick 2 1 3
3 cricket 3 Harry 3 2 1
... ... ... ... ... ... ...

Now the query should run something like: select all members of the
volleybalteam ( teams.idguy where idsport=1, and that joined on guys, etc),
and return that in the following single record:

sportsname team
volleybal Tom, Dick

The reason I ask is that I need that record for further processing
elsewhere, so solutions involving forms in any way are no use.

Thanks in advance,

WS

[Peter Franklin]

Hi WS

Dev Ashish wrote a function called fConcatChild to do this.
you'll find it on:

http://www.mvps.org/access/modules/mdl0004.htm

HTH

Peter Franklin

On Tue, 13 Jan 2004 00:20:41 GMT, wn********@yahoo.com wrote:

Dear all,

Suppose there is a table whose records contain a textfield TXT, and a query
that returns a subset of that table. Is it possible to have that query
return all the TXT-fields gathered together in one string? I really have no
idea as to how to tackle this. For clarity, here's an example of what I
mean:

there are three tables

sports guys teams
id name id name id idsport idguy
1 volleybal 1 Tom 1 1 2
2 basketball 2 Dick 2 1 3
3 cricket 3 Harry 3 2 1
... ... ... ... ... ... ...

Now the query should run something like: select all members of the
volleybalteam ( teams.idguy where idsport=1, and that joined on guys, etc),
and return that in the following single record:

sportsname team
volleybal Tom, Dick

The reason I ask is that I need that record for further processing
elsewhere, so solutions involving forms in any way are no use.

Thanks in advance,

WS

[Lyle Fairfield]

wn********@yahoo.com wrote in
news:40*********************@dreader13.news.xs4all .nl:

Dear all,

Suppose there is a table whose records contain a textfield TXT, and a
query that returns a subset of that table. Is it possible to have that
query return all the TXT-fields gathered together in one string? I
really have no idea as to how to tackle this. For clarity, here's an
example of what I mean:

there are three tables

sports guys teams
id name id name id idsport idguy
1 volleybal 1 Tom 1 1 2
2 basketball 2 Dick 2 1 3
3 cricket 3 Harry 3 2 1
... ... ... ... ... ... ...
Now the query should run something like: select all members of the
volleybalteam ( teams.idguy where idsport=1, and that joined on guys,
etc), and return that in the following single record:

sportsname team
volleybal Tom, Dick

The reason I ask is that I need that record for further processing
elsewhere, so solutions involving forms in any way are no use.

Thanks in advance,

WS

This function (as an example) might help.

You could modify it to match your table and fields. GetString take
parameters as to delimters.

And set one of the calculated fields in your query to return it.

CurrentProject.Connection.Execute("SELECT Distinct fldDescription FROM
tbl2002Transactions ORDER BY fldDescription").GetString()

.... assumes AC2K or greater.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[wnstnsmith]

Thanks to all, but this was by far the best answer. It is a beautiful base
to work from

WS

Dev Ashish wrote a function called fConcatChild to do this.
you'll find it on:

http://www.mvps.org/access/modules/mdl0004.htm

HTH

Peter Franklin