Concatenate returned query records into single field

[Rank Am]

Hi,
Does anyone know if there is a simple SQL aggregate fuction similar to SUM or COUNT to concatenate field values into a single field across a one to many join? At the moment I am using ADOX to create temp tables on the fly on the server and loop through two recordsets (one on the group by side and the other on the many side of the join) and numerous INSERT INTO statements. I then have to set the form / report recordset to be the recordset based upon the temp table.
I have a few more of these to create and it seems like alot of work as opposed to using the word SUM in SQL to add values.

Thanks

Jon

 

[Satal Keto]

I have given a small example below which uses two fields in a table called 'Table1' and concatenates them.
Hopefully this will point you in the right direction

SELECT Table1.Forename, Table1.Surname, [Table1].[Forename] & " " & [Table1].[Surname] AS Fullname
FROM Table1;

 

[Rank Am]

Thanks but that is concatenating 2 values from the same record. I want to concatenate distinct values from same field from many records, vertically as opposed to horizontal. Similar to the SUM aggregate function.

 

[Satal Keto]

Yeah that would be the same row, sorry.

Umm... I can't think of anyway of doing that through SQL so I guess you would have to use VBA (unless someone else knows a way which I don't)

 

[datAdrenaline]

You can use the code found here ...

http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1190033

Its called MakeCSV() ... you can pass to it a SQL Statement, DAO rst, ADO rst, or Multi-select list box ...

You can use the MakeCSV() function call in a query, which make it conveinient.

...

If you'd like to "Roll your own" CSV generating type code, I would highly suggest the .GetString method of an ADO recordset object.

 

[Rank Am]

Thanks for your help, works well.

Regards

Jon