return mulitple values in single query field

[danwand]

Hi,

The problem i have is quite difficult to explain, so i will start with a simplified problem that may set me on the way!

I have 2 tables; tblAuthorites (auID(PK), Name, Address)tblSocialWorkers (swID(PK), auID(FK), Name Address)

I want to create a query that for each authority lists the related social workers.

However i want the results to be displayed as follows

AUTHORITY SOCIAL WORKERS
Kent John Smith, Ian Bell
Surrey Paul Edwards
Lincoln Sue Jones, Mike Reed, Paul Collins

(So i'm trying to place multiple values in a single query field)
Is it possible to retrieve a result like this using subqueries rather than using a function to get the social worker names?

As i said this is a simplified version of the problem, if this is possible then i will also have to factor in some additional criteria for retrieval as well as doing a similar thing with a number of other tables as well.

Any help or advice would be appreciated.

Dan

 

[JerryKlmns]

Does this help?
How to concatenate multiple child records into a single value
faq701-4233

 

[danwand]

Thanks for the reply,

I have actually created a similar type of function before, but was actually wondering if the results were possible by just using sql/subqueries without using a function,

Cheers
Dan