how to concat from another table field w/in my select?

[ZX]

Lets say I have 3 tables: t_Process and t_Task.

Every process has tasks. For example, the process of Change Management has
multiple tasks: Getting the fix programmed, tested etc. So there's a 1-many
relationship between tasks and processes.

Now my question: t_Task has a field called 'system'. This means, what
system is this task associated with. I want a query to show for each
Process, all the systems that apply to it, in 1 CELL - separated by commas.
In other words, I want it to show for a given row:

Cell 1: Change Management
Cell 2: VB App1, Powerbuilder App2, .NET App3

Obviously I need to concatonate here. But I'm not a guru w/ Access. I'm
thinking this belongs in a VB module, but not sure - can I query from a VB
module? In Oracle, this would be easy: I just create an Oracle function or
proc that performs a select in a loop, and returns a string - based on the
Process_id that I pass to the function. I need to do that with Access. I
need to pass something the procedure, and then have it look up all the tasks
associated with that procedure and for each one - take the system from the
system field, and start building a string. Then return it at the end....

Any ideas how to do this. Thanks!


I can already hear you now wondering why I need to do that - but guess what
- I do. I can't create a separate query on t_

 

[Marshall Barton]

Lets say I have 3 tables: t_Process and t_Task.

Every process has tasks. For example, the process of Change Management has
multiple tasks: Getting the fix programmed, tested etc. So there's a 1-many
relationship between tasks and processes.

Now my question: t_Task has a field called 'system'. This means, what
system is this task associated with. I want a query to show for each
Process, all the systems that apply to it, in 1 CELL - separated by commas.
In other words, I want it to show for a given row:

Cell 1: Change Management
Cell 2: VB App1, Powerbuilder App2, .NET App3

Obviously I need to concatonate here. But I'm not a guru w/ Access. I'm
thinking this belongs in a VB module, but not sure - can I query from a VB
module? In Oracle, this would be easy: I just create an Oracle function or
proc that performs a select in a loop, and returns a string - based on the
Process_id that I pass to the function. I need to do that with Access. I
need to pass something the procedure, and then have it look up all the tasks
associated with that procedure and for each one - take the system from the
system field, and start building a string.

You're right, you need a function to do that. I like the
one at:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

 

[ZX]

thanks Marshall for the info.
note - thats a dead link though and looking around on his site i couldn't
find anything titled 'generic function to concatenate child records'

 

[Duane Hookom]

Check this link
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane