append multiple row values into a single field in a single row

Question

Hi, I effectively want to "transpose" some data.   I have some company names listed in a table and I would like to get them appended into a single field in a single row in another table and simply separated by commas.  Is there some generic code that will help me achieve this?

eg table lists as follows:-

"Company A"

"Company B"

"Company C"

etc

and I wish to get that data into a single field showing as "Company A, Company B, Company C" etc

Thanks

Dan

Answer 1

Hi Dan,

See if this simple function will do what you want: SimpleCSV()

You should be able to use it in an UPDATE query.

Hope it helps...

Answer 2

You don't need to insert the concatenated values into a column in a base table, nor should you do so, as it immediately opens the database to the risk of update anomalies.  You can return them in a query's result table using a concatenation function such as that to which theDBguy has given you a link.  You'll also find one which uses the highly efficient GetString method of the ADO recordset object, rather than the usual method of looping through a DAO recordset, in Concat.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the 'Concatenate values from a related table' option illustrates the use of a GetList function which calls the GetString method to concatenate values in computed controls in a report, returning the products ordered per customer.  If you are dealing with just the one table, then the function could of course concatenate values from a column in that table of course and return the single string expression in a query, e.g. using Northwind data:

SELECT GetList("Customers","Company","ID",",") As CompanyList;

Which returns a single row/column result table:

Company A, Company B, Company C, Company D, Company E, Company F, Company G, Company H, Company I, Company J, Company K, Company L, Company M, Company N, Company O, Company P, Company Q, Company R, Company S, Company T, Company U, Company V, Company W, Company X, Company Y, Company Z, Company AA, Company BB, Company CC
 

---

Ken Sheridan, Stafford, England