By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,623 Members | 1,892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,623 IT Pros & Developers. It's quick & easy.

concatenate strings from 2 tables but only if value not already there

P: 1
I have a table (TableA) of contacts that i am uploading from Excel.
One column is called 'Sector'.
The field in Excel contains a Sector name which i want to include in the Access Record, but if the record appears on several Excel sheets, sometimes with the same Sector, i get repition of Sector in that field. It should only appear once.
At the moment, i upload to a temp table (TableB).
I have a 3rd table (TableC)
An append query concatenates TableA, a semi-colon, and TableB into TableC .
An update query then updates tableA to the values in TableC.
I can't seem to get criteria to check if Sector in TableB already exists in TableA, if so skip, else add with simicolon.
Any ideas or help would be much appreciated.
Jun 24 '15 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 15k+
P: 31,769
I'm not very clear on what's repeating where or why. Nevertheless, the following advice should provide a viable way to work it :
  1. Get the data so that it's accessible to Access one way or another. The simplest is to link a table to the spreadsheet.
  2. Design an APPEND query that uses the DISTINCT predicate to add only unique items to the [Sector] table.
  3. Design a separate APPEND query that takes that data from your spreadsheet linked to the [Sector] table on the Sector name and include the reference to add to the field in your TableA instead of the description itself.
Jun 25 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.