I cannot figure out this query - I thought it would be simple but...

[su]

I am having trouble creating a query that ouputs each of the children
associated with a parent. Here is a simplification of my tables:

tblParent
Parent_ID
A
B
C

tblChild
Child_ID Parent_ID
1 A
2 A
3 A
4 B
5 C
6 C

Desired Result:
Parent_ID Children
A 1,2,3
B 4
C 5,6
The desired result set would have the parent as well as each of the
parent's children concatenated together. Any ideas how to do this in
SQL? I could do it in code, but unfortunately it's not an option in
this case. I need to do it in an Access query since I need the results
in a query for a Word merge.

Any help would be appreciated!!!!!!

[Alan Webb]

Su,
SQL isn't really made to do this. You could use a cross-tab in Jet to do
this or the documented method of making cross-tab views in SQL Server using
CASE but even then you end up with a two-dimensional matrix. To convert
this to delimited text in the format you want will still require code. I'd
charge overtime to write this and make sure I got paid half up front in
combat pay. If my customer balked I'd refuse to write it and consider
myself lucky to be free of an obnoxious client.

"su" <sb****@canoemail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I am having trouble creating a query that ouputs each of the children
associated with a parent. Here is a simplification of my tables:

tblParent
Parent_ID
A
B
C

tblChild
Child_ID Parent_ID
1 A
2 A
3 A
4 B
5 C
6 C

Desired Result:
Parent_ID Children
A 1,2,3
B 4
C 5,6
The desired result set would have the parent as well as each of the
parent's children concatenated together. Any ideas how to do this in
SQL? I could do it in code, but unfortunately it's not an option in
this case. I need to do it in an Access query since I need the results
in a query for a Word merge.

Any help would be appreciated!!!!!!

[pietlinden]

use fConcatChild from access web on tblChild. then if you want, you
can query that and stuff names in.

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