Solved: Combine values into a field

[bkudulis]

I have a table that holds the many side of delays. The values are held in a field called Delay. I would like to combine the values into 1 field of all of the delays.

Ex. Record1 has 4 delays A, B, C, F. I would like to have the field in a query display A,B,C,F

[mdmackillop]

Is this what you're after?

SELECT [a] & "-" & [b] & "-" & [c] & "-" & [f] AS delay
FROM Table1;

[bkudulis]

If the field names were A B C then that would work but I want to combine all of the values from field delay into one. The query would return say 3 records. The first record would be A
The Second record would be B
The Third record would be C


I want a field to contain A,B,C

[Cosmos75]

What are you trying to accomplish?

Are you saying if you had something like this in a table
Product ID, Reference, Description
2211, C1, 10uF 15V
2211, C3, 10uF 15V
1392, C6, 22uF 15V
0100, R3, 10K
0100, R4, 10K
013, R2, 4K7

You want to end up with a query that returns something like this
Product ID, Reference, Description
2211, "C1,C3", 10uF 15V
1392, "C6", 22uF 15V
0100, "R3,R4", 10K
013, "R2", 4K7

[bkudulis]

Exactly

[Cosmos75]

I just answered a similiar question posed by someone at DBForums.com, which is where I got my example.

Here's the link to the article on my website;
Concatenating unique field values for records matching a query's GROUP BY clause (http://www.accessdb.info/index.php?option=com_content&task=view&id=102&Itemid=45)
(I know the title is a mouthful. If anyone can think of a better one, I'm all ears!)

As I point out in the article, I don't really recommend doing this in a query as it can be really slow (at least with my VBA function). Maybe someone here will know a better way. I'd sure be interested!

A better way is probably to create a temporary table. For example, create a query to with GROUP BY clauses, or perhaps a SELECT DISTINCT query. Then, for each record returned by the query, loop through the ungrouped data source to find the unique values for the field you want to concatenate (grouped the same way as our hypothetical query) and write the query's results and the concatenated string to the temporary table. The logic is pretty much the same as the VBA function I wrote.

Hope this helps or at least gives you a starting point!

[bkudulis]

This was exactly what I was looking for, thank you very much.

[Cosmos75]

bkudulis,

Glad to hear it worked for you!

FYI, I tweaked the VBA code a little http://vbaexpress.com/forum/images/smilies/024.gif and also found some differences between how it works in Access 97 versus Access 2000 and higher. So, I created and uploaded new samples files (with more complicated data) and edited the article a little. I recommend looking at the new version of the sample file.

Edit: 6/7th/2006 - updated the code to handle GROUP BY clauses that include Date/Time fields. Sample files were also updated to reflect revised code