[AccessD] Re: To Combine Child Records

Actually, you can do this with 2 views combined in one and possible using a 
subquery.

You can create a SQL Server function that can give you the following:

1001, "Spreadsheets,GenStat"

Once you get that, it is a simple join to get the name added in.

Robert

P.S.  I covered the function that was required to do this in my last Access 
developer's workshop.  We did it in both Access and SQL Server.

At 12:00 PM 4/26/2004 -0500, you wrote:
>Date: Sun, 25 Apr 2004 22:22:49 -0700 (PDT)
>From: S D <accessd667 at yahoo.com>
>Subject: Re: [AccessD] OT SQL Question
>To: Access Developers discussion and problem solving
>         <accessd at databaseadvisors.com>
>Message-ID: <20040426052249.23200.qmail at web61108.mail.yahoo.com>
>Content-Type: text/plain; charset=us-ascii
>
>Martin,
>
>I've checked it out during the weekend (for a short while...it IS a 
>weekend he). I don't see how you could create this using SQL. PERHAPS, 
>using COMPUTE?! I'm not very familiar with this keyword.
>
>To save a lot of time and (very) complex coding...just use a cursur. Add 
>each value to a variable and use this in an insert statement.
>
>Regards,
>
>Sander
>
>PS: Oracle to retrieve SQL-Server data....oi
>
>Martin Reid <mwp.reid at queens-belfast.ac.uk> wrote:
>Sander
>
>See inline
>
>Martin
>
>
>
> > Martin,
> >
> > can you please provide a little more info?
> > WHY do you not want to use cursors?
>
>No reason just wanted to try it using SQL but didnt seem
>possible without cursors.
>
> > Where does the output have to go to?
>
>Into a table
>
> > What format does it need to have?
>
>see above
>
> > What's the layout of the tables? (Send me the create table scripts...)
>
>No problem will email them of line later on. That OK?
>
> >
> > Sander
> >
> > Martin Reid wrote:
> > Tried the SQL Server list with this - no luck.
> >
> >
> > Given a one to many relationship is it possible to produce this output 
> using
> > an SP without the use of cursors etc??
> >
> > INPUT TABLE 1
> >
> > 1000
> > IT
> >
> > 1000
> > Databases
> >
> > 1000
> > VMS
> >
> > 1001
> > Spreadsheets
> >
> > 1001
> > GenStat
> >
> > 1002
> > Aquatic systems
> >
> > 1002
> > Research
> >
> >
> >
> >
> > INPUT TABLE 2
> >
> >
> >
> > 1000
> > Smith
> >
> > 1001
> > Brown
> >
> > 1002
> > Foster
> >
> >
> >
> >
> > OUTPUT REQUIRED
> >
> > 1000
> > Smith
> > IT, Databases,VMS
> >
> > 1001
> > Brown
> > Spreadsheets,GenStat
> >
> > 1002
> > Foster
> > Aquatic Systems,Research
> >
> >
> >
> >
> > Martin