concatenating on the many side (1 Viewer)

[bauer]

I wanted to ask any of you out there for some advice on how I am going about this.

I am taking two tables, family and children, where a family can have many children.

I need to create a new table identical to the family table (with all of it's fields), and just add a new column called childNames, which will be a csv of all their children.

The main reason I'm asking for help is the way I have done it so far takes about 26-32 seconds, which I would like to trim down (though it seems like much longer).

Now for the code:
after I create the table, and add the column...

[COLOR=green]'childrenNames is the table I just created[/COLOR]
Set rs = db.OpenRecordset("childrenNames")

[COLOR=blue]Do While Not[/COLOR] rs.EOF
   currentFamilyID = rs("ID").Value
   [COLOR=blue]Set[/COLOR] rsChild = db.OpenRecordset("SELECT FirstName FROM Children WHERE ID=" & currentFamilyID)
   childNames = ""
   [COLOR=blue]Do While Not[/COLOR] rsChild.EOF
      childNames = childNames & ", " & rsChild("FirstName")
      rsChild.MoveNext
   [COLOR=blue]Loop[/COLOR]
   childNames = Right(childNames, Len(camperNames) - 2)
   rs.Edit
   rs("First Names") = childNames 
   rs.Update
   rs.MoveNext
[COLOR=blue]Loop[/COLOR]

So basically, I am wondering if anybody out there might have a better idea for this than me.

Thank you all for your help!

-Bauer

 

[WayneRyan]

Bauer,

I don't think that you really want to create this other
table.

The maintainence code will be a nightmare.

You can always traverse the "children" and generate
a comma-seperated value list.

There is also a link (somewhere) on this site that
shows how you can report these "economically".

Wayne

 

[bauer]

Thank you for your response WayneRyan,

The thing is I'm doing this for a client of mine, and this is alreayd how he set up his database. He has several other queries and reports using the data from this table. I was thinking of going the route of a query, but I couldn't figure out how to do that neatly.

However, if you have that link, that would be great, as I did try a bit of searching here, but I didn't find anything other than a link to a MS KB article that was for a report, and pretty much said you had to loop through the whole recordset, so I figured this would be the closest thing.

thanks for your help!