Running Concatenation (1 Viewer)

[Freddy]

I am trying to create a simple query that will keep a running list of first names, but am having problems. It was done in the design view but here's the sql for discussion purposes:

PARAMETERS RunningList Text ( 255 );
SELECT Family.LastName, ([RunningList] & ", " & [FirstName]) AS RunningList
FROM Family
WHERE ((Family.LastName)="Fleming");

Eventually I'd like to be able to update the RunningList parameter based on some criteria, but can't get the first part to work.

Here's the output:

LastName RunningList
Fleming , Linda
Fleming , Vivian
Fleming , Jacqueline
Fleming , Rosena

I was trying for something like:

Fleming , Linda, Vivian, Jacqueline, Rosena

I've seen code that will do this, but would like to know if it is possible w/o programming first.

 

[Pat Hartman]

You can, but only if you can limit the numer of names to be concatenated. You could do it by adding the table to the QBE grid as many times as you might have names. You would join the second through nth table instances to the first table with left joins. You could then concatenate the names:
Select tbl1.LastName * ", " & tbl1.FirstName & ", " & tbl2.FirstName & ", " & tbl3.FirstName etc.

You would end upu with a bunch of trailling comas. "Jones, Tom, Mary, Ed, , , , , , , ,"

You're much better off using the VBA code you have.

 

[Freddy]

Pat, thanks for the reply. Never thought of that one. Another option I was thinking of was some manipulation of results from a cross tab query, but your right, its best coding.