Can I Join two tables with Partially matched fields

I think there are ways to have a Join two table with partially matching fields of each table. But Only exact match were found in the help. Please Help me

Say, i have a table

Table1
ID, FieldA
===================
1    RestaurantPizzahut
2    PizzaHut
3    Pizzahut Rest.

Table2
Categ     Field
AA-bbb    PizzaHut
AA-ccc    SteakHouse
AA-ddd    CentralMeal

Than I wanto have a resulting table like this

id   AddrCode    Field
1    AA-bbb      PizzaHut
2    AA-bbb      PizzaHut
3    AA-bbb      PizzaHut

Direct Query Statement For Access(SQL) is fine.
or Direction via UI is also o.k

Thanks in advance
 
qkrwngmlAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
You can do joins that are simply based on equality.  In this case, you could try something like:

SELECT t1.ID, t2.Categ, t2.[Field]
FROM Table1 t1 INNER JOIN
    Table2 t2 ON t1.FieldA Like "*" & t2.[Field] & "*"
0
 
jmoss111Commented:
Hello qkrwngml,

Or you could build a column of portions of several columns to create a match key, would be sort of like a hash key

Regards,

Jim
0
 
k_murli_krishnaCommented:
A more accurate/readable query is like:
SELECT t1.ID, t2.[Categ] AS AddrCode, t1.[FieldA]
FROM [Table1] t1, [Table2] t2
WHERE t1.[FieldA] LIKE '%PizzaHut%' AND t2.[Field] = 'PizzaHut';

In SQL Server wild card character maybe * instead of %. You can write satndard/recommended ANSI join syntax like mathewspatrick which helps in filtering records in FROM clause which executes first and then further filtering in WHERE clause.

0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this

select Table1.ID, Table2.Categ as AddCode, Table2.FieldA
from Table1, Table2
where (instr([table1].[fieldA],[table2].[FieldA]))>0
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.