Solved

Non-Exact match Lookup Table

Posted on 2008-10-13
4
313 Views
Last Modified: 2013-11-27
I want to update table1.Code with table2.Code by matching the value of table1.Description to the data in table2.MatchingText where the two are not exact matches (eg. table1.Description LIKE table2.MatchingText), essentially a non-exact match lookup table.

Tables are structured as:

table1
-------
recNum
Description
Code

table2
-------
MatchingText
Code

It seems like it should be easy, but I'm at a loss.
0
Comment
Question by:LERNWebmaster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22708218
You are pretty close, but since you don't have an exact match you have to do something like this:

table1.Description LIKE "*" & table2.MatchingText & "*"
0
 

Author Comment

by:LERNWebmaster
ID: 22708232
and how would I link those?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 22708242

UPDATE Table1 t1 INNER JOIN Table2 t2
ON t1.Description LIKE "*" & t2.MatchingText & "*"
SET t1.Code = t2.Code

Open in new window

0
 

Author Closing Comment

by:LERNWebmaster
ID: 31505760
Thanks.  I'm going to go smack myself for being so lame now. :-)
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question