Combining Column Data

[philljp390]

Hi,

I have a table structured as follows (for example):

Field1----Field2
1--------House
2--------House
2--------Car
2--------Car
3--------Tree
3--------Car
4--------House
4--------Tree

I want to combine the values in Field2 into one Field, for example:

Field1----Field2
1--------House
2--------House, Car, Car
3--------Tree, Car
4--------House, Tree

How can I do this in a query, any help appriciated.

 

[Jon K]

To do it in a query, you will need to use VBA code.

I have attached a database that contains your sample data and a Totals Query. The query uses a function Conc() to concatenate Field2. The code of the function is in Module1.

 

[Bort]

I need to do the same thing, except I have a few more wrinkles. This is an inventory list that needs to have one field shortened down like that. I am gonna try to just edit your code to make it work, but I'm completely new to VBA and SQL, so I'm kinda hapless. If you could point out how to do this, I'd be eternally grateful.

DCS VC Desc1 Desc2 Attr Cost Retail
12 TB Polo Shirt 5789 BLU 45 90
12 TB Polo Shirt 5789 BLU 45 90
12 TB Polo Shirt 5788 PNK 45 90
12 TB Polo Shirt 5788 PNK 45 90
12 TB Polo Shirt 5787 BLK 45 90
12 TB Polo Shirt 5787 BLK 45 90

It looks like that now, what I want is something like:

DCS VC Desc1 Desc2 Attr Cost Retail
12 TB Polo Shirt 5789, 5788, 5787 BLU, PNK, BLK 45 90

*Eternally grateful* in advance

 

[Jon K]

I have modified the function in the database.

 

[Bort]

I couldn't get it to work using that exact form. It always gives me a syntax error on the Conc([DCS], "Desc2") thing. When I deleted the Desc2 in quotation marks it runs, but the Attr and Desc2 fields are blank.

 

[Jon K]

It's strange. I just downloaded the database and ran the query "Query1". I got these results:-

DCS	VC	Desc1		Desc2			Attr		Cost	Retail
12	TB	Polo Shirt	5787, 5788, 5789	BLK, BLU, PNK	$45.00	$90.00
222	AAA	aaaa aaaa	6666, 8888		BLK, YEL	$50.50	$98.50

Desc2 and Attr were there, properly concatenated.