Concatenate fields without duplicating data

[Rachel]

I have two tables that I'm creating a query with.

Table 1 has Agent field and Table 2 has product field. I'm linking the
tables by a primary key.

Table 1 has some product names in the Agent field (Commercial, Residential,
Commercial & Residential). I would like to concatenate all of the three
product names from the Agent field in Table 1 to the product field in Table
2. Table 2 may already have that particular product name in the product
field, in this case I should not concatenate it. In any case, I will need to
delete these three product names from the Agent name in Table 1.

I also need to change the word "Residential" to "Personal".

Example:

Rec # SG_Carrier PRODUCTS CITY
1 Progressive Personal Anchorage
2 Nationwide Life Anchorage
3 Commercial Personal Birmingham
4 Residential & Commercial Life Alexander City
5 Residential & Commercial Commercial Hartselle
6 Residential & Commercial Bonds, Commercial Tuscaloosa
7 Residential Dumas
8 Commercial Flood Only Tustin
9 Residential & Commercial Bonds, Commercial Mena
10 Residential & Commercial Commercial, Specialty Fresno

The above needs to be changed to:

Rec # SG_Carrier PRODUCTS CITY
1 Progressive Personal Anchorage
2 Nationwide Life
Anchorage
3 Personal, Commercial Birmingham
4 Life, Personal
Alexander City
5 Commercial, Personal Hartselle
6 Bonds, Commercial, Personal Tuscaloosa
7 Personal Dumas
8 Flood Only, Commercial Tustin
9 Bonds, Commercial, Personal Mena
10 Commercial, Specialty, Personal Fresno

I hope that's not to confusing.

Any help would be greatly appreciated.

Thanks,
Rachel

 

[PieterLinden via AccessMonster.com]

use fConcatChild() from Access web. It's in the modules section.