Concatination of certain records

[chrismarshall81]

Hi I need to concatenate some data for a report but unsure how to do it. Basically I have data that looks like this:

IDProductComp 1prod 1Comp 1prod 2Comp 1prod 3Comp 1prod 4Comp 2prod 5Comp 2prod 6Comp 2prod 7

But I want it to look like this:

IDProductComp 1prod 1, prod 2, prod 3, prod 4Comp 2prod 5, prod 6, prod 7

Any ideas?

Thanks in advance

 

[MarkK]

Maybe you can describe in more detail where you are stuck. Do you just need to delete the "1prod" and "2prod" substrings and add some commas? Maybe this gives you some ideas...

dim tmp as string
tmp = Replace("IDProductComp 1prod 1Comp 1prod 2Comp 1prod 3Comp 1prod 4Comp 2prod 5Comp 2prod 6Comp 2prod 7", "1prod ", "")
debug.print tmp

 

[chrismarshall81]

Apologies I noticed the table I pasted in didn't come through properly. I want to be able to turn this sample data set:

ID Product
Comp1 Prod 1
Comp1 Prod 2
Comp1 Prod 3
Comp1 Prod 4
Comp2 Prod 5
Comp2 Prod 6
Comp2 Prod 7

Into this

ID Product
Comp1 Prod 1, Prod 2, Prod 3, Prod 4
Comp2 Prod 5, Prod 6, Prod 7

Apologies about the format, can't seem to paste a table into this forum. This is obviously just a sample data set, I have around 20k records where one ID has 1 Product and others have up to 10

Thanks again in advance

 

[MarkK]

Hi, I think you will need to...

open a recordset, 
loop thru it, and 
   [COLOR="Green"]'while looping test for a change in Comp[/COLOR]
   if comp changed
      end and save your 'comp & prod' concatenation for the old value of comp
      restart your prod concatenation for the new value of comp
   else
      concatenate prod
   end if
end loop

Hope that helps,