asked on

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

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jmoss111

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

k_murli_krishna

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.

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial