Access SQL: How of return multiple records in one single field 2

[Clipper2000]

I am developing an Access database for a local Adult video store. So far I am the following tables:

1). Titles: this table list all the movie titles with an autonumber data type, along with the studio, media type and related data.

2). Stars: Actors and actresses found in each title (link to the title table via a numeric field).

For example, assume autonumer 1 is "Debbie Does Dalllas", and 2 is a different movie title, the contend in Stars table would look like:

TitleNo star
1 Bambi Woods
1 Misty Winter
1 Pat Allure
1 Rikki O'Neal
1 Arcadia Lake
2 Peter North
2 Juliet Anderson
2 Ginger Lynn

I would like the query to return something like this:

Title Star
1 Bambi Woods, Misty Winter, Pat Allure, Robyn Byrd
2 Peter North, Juliet Anderson, Ginger Lynn

I can do this using ADO and VB. However, the reason I ask is we will soon migrate to SQL server and I would like to make uses of the server side SQL processing capability instead of processing it locally. Your help will be greatly appreciated.

Clipper2000

 

[dhookom]

You might want to look at

http://www.sqlteam.com/item.asp?ItemID=2368.

See the "new and improved approach".

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Crowley16]

I'm not sure if the sql server syntax is the same as jet, so when you migrate, the query probably won't work anyway...

--------------------
Procrastinate Now!

 

[PHV]

In sql server the way would probably be playing with stored procedure or user function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Clipper2000]

Thank you all for your great tips !!!

I solved my problem in 5 min by designing a user defined function which accepts the title no as a parametre, have it go through a loop, added a comma, and have the function return the desired commas separated value. I then use this result as my new field value.

Great site !