A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Concatenate fields without duplicating data



 
 
Thread Tools Display Modes
  #1  
Old May 18th, 2010, 01:20 PM posted to microsoft.public.access.queries
Rachel
external usenet poster
 
Posts: 187
Default Concatenate fields without duplicating data

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

  #2  
Old May 18th, 2010, 01:50 PM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default Concatenate fields without duplicating data

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:44 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.