concatenate text

[datacontrol]

I have a monumental task at hand. I have a table with a field that needs merged. Below is a sample of my data:

ID ListID ContactID ProfileQ Response Description
1 22403 1110 02 1 Cosmetic Surgery
2 22404 1110 03 5 Office Nurse
3 22405 1110 04 2 Physician's Office
4 22406 1110 05 2 Medical/Surgical
5 22407 1110 06 1 High School
6 22408 1110 07 0 Unknown/None

What I need to do is build a query that takes the description and places them all in one record per contact id. It does not matter to me if the description appears in one field or multiple fields pers record. Any ideas?

 

[Jon K]

You can do it with a series of two queries.


I have attached a database. You can open it and run QueryTwo.

Note these limitations:-
(1) As DCount() is used in QueryOne, if the table is large, running the queries will take time.

(2) As the maximum number of fields in a table/query is 255, the number of descriptions (i.e. records) for each ContactID cannot exceed 254.


The database was saved from Access 2000.