Create Linktable with concatenate or join?

[#1]

I have a table looking like this

PROJECT

idproject	product
1	;prod1;prod2;
2	;prod2;
3	;prod2;
4	;prod1;prod3;
5	;prod3;

I have now want to make a table looking like this

idproject	product
1	;prod1;
1	;prod2;
2	;prod2;
3	;prod1;
4	;prod1;
4	prod3;
5	prod3;

This table will then be a linktable between project and a table looking like this:

product	ProduktName
;prod1;	Window
;prod2;	Locker
;prod3;	Stone

I have tried to create the linktable by concatenating tables from PROJCET like this:

LinkProduct:

Load idproject,

     product as ProductKey

Resident product like '*;Prod1;*'

Concatenate (LinkProduct)

Load idproject,

     product as ProductKey

Resident product like '*;Prod2;*'

Concatenate (LinkProduct)

Load idproject,

     product as ProductKey

Resident product like '*;Prod3;*'

But this will not create one uniqe row for each product.

Someone that could help

[#2]

Use JOIN, if Product Already exists, then It will do a JOIN and If Dosenot it will Add a New Entry in the link Table.

[#3]

If i do a join it will not work. A join will give me a linktable looking like the old table:

[#4]

Anyone pleas help with me with this problem.

It should not be so complex.

I need to create a table where I can choose from one of the products and if this product is presented in a project that project should be included.

For example pressing Window (prod1) in the table above should select the project with id 1 and 4 since these projects have the product Window.

[#5]

Hi Samir,

Take a look at my example.

I hope is what you want.

Best regards.

[#6]

This look very good.

But as I look into the code it looks like you only can handle 3 product, or am I wrong? I have total 10 procucts.

Maybe if you look at this pic you vill understand how I think.

[#7 · Accepted solution]

Hi Samir,

The code will work fine for all products you have.

Please have a look again.

Best Regards.