Need to concatenate duplicate values

Question

I have a table with the (sample) data as follows:

Product	Customer	Demand Note
111111	Gail	Gail Dec 20
111111	John	John   Jan 30
111111	Susan	Susan Dec 35

I want to be able to run a query where the results come out like this:

Product	Demand Note
111111	Gail Dec 20,John Jan 30,Susan Dec 35

The actual lists that I am using are huge.  Trying to figure out a way to consolidate the note if the product number is duplicated.  I have tried ConcatRelated (cannot figure out how to make it work), I have tried Crosstabs, etc. 

Any help is greatly appreciated.

Answer 1

Just to add one more alternative to the mix you can try this generic concatenate function http://www.tek-tips.com/faqs.cfm?fid=4233

Answer 2

There are various concatenation functions available, including one in Concat.zip in my public databases folder at:

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

This includes a solution using the very efficient GetString method of the ADO recordset object to concatenate values.  As an example it uses data from Northwind.  While such a function would in most cases be used to concatenate values from a referencing table, there is no reason why it's should not be used with a single table.  I hope that you do have a referenced Products table or similar, to which the current table is related in an enforced relationship?  

Also, are you really repeating the customer name in the DemandNote column?  It's a very bad design point if you are as not only does it introduce redundancy, with the ensuant danger of update anomalies, but it represents two attributes (the customer and date) in a single column.

Allen Brown also provides a concatenation function (possibly that which you've tried) at:

http://allenbrowne.com/func-concat.html