Combine multiple rows of data into one long string

[pocquet]

Hi guys,

I have a data set that is by its nature has multiple lines for a single reference number.

So for example:

[FONT=Calibri][COLOR=#000000][/COLOR][/FONT][TABLE]
<caption>[B]Qry_Master_Vendor_Output_L1[/B]</caption><thead>[TR]
[TH="bgcolor: #c0c0c0"][FONT=Calibri][COLOR=#000000]CPVDescription[/COLOR][/FONT][/TH]
[TH="bgcolor: #c0c0c0"][FONT=Calibri][COLOR=#000000]Vendor[/COLOR][/FONT][/TH]
[/TR]
</thead><tbody>[TR]
[TD][FONT=Calibri][COLOR=#000000]Sludge disposal services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Sludge treatment services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Sludge transport services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Sludge removal services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Treatment and disposal of foul liquids.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Refuse incineration services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Ash disposal services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Household-refuse disposal services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Urban solid-refuse disposal services.[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]1000049892[/COLOR][/FONT][/TD]
[/TR]
</tbody><tfoot></tfoot>[/TABLE]

Is it possible to combine all results in CPV descirption so they appear as a single row eg:

[TABLE="width: 328"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]CPVDescription[/TD]
[TD]Vendor[/TD]
[/TR]
[TR]
[TD]Sludge disposal services.Sludge treatment services.Sludge transport services.Sludge removal services.Treatment and disposal of foul liquids.Refuse incineration services.Ash disposal services.Household-refuse disposal services.Urban solid-refuse disposal services.[/TD]
[TD]1000049892[/TD]
[/TR]
</tbody>[/TABLE]

But with spaces rather than the period?

Thanks

 

[pocquet]

HI,

effectively i want a query in access OR a macro in excel that concatenates the field CPVvendor description where it finds multiple vendors,

The reason is I need to create a One to One relationship that will allow me to look up the amount spent with the supplier over a year.

The CPVendor I then use later on to perform a Keyword search for end users in excel that then brings back the required hits (I already have this working)

Thanks

Poc

 

[Micron]

Often, I don't ask why someone wants to do something because it tends to complicate arriving at a solution; especially if the request seems straight forward. In this case, I probably should have.

The reason is I need to create a One to One relationship that will allow me to look up the amount spent with the supplier over a year.

Given your sample data, I fail to see how concatenating all these words is necessary to calculate a spend for a supplier for a time period. This is easily accomplished with a Totals query that Groups By supplier.

I'm away for a while and will do my best to follow your replies, but I may not be able to respond rapidly.

 

[james_lankford]

maybe the recordset GetString method wil ldo what you want

https://msdn.microsoft.com/en-us/library/ms676975(v=vs.85).aspx

Export to a text file with the GetString method