Can anyone help me with this? I'm not sure if I have to use VBA or a simple SQL statement.

I am currently designing a database and I have come across a bump. I have two tables that have similar data but do not have exact values being similar in each table. I am trying to relate the tables through a query that uses the values of table1.Name and searches for each value in table2.Name.

Eventually I will relate the results to a second similar search (using the address fields) to cross reference the results.

The database is designed like this.

Table1
ID (unique to table, not in common with table2.ID
Name (one word)
Address (Street address)

Table2
ID (unique to table, not in common with table1.id
Name (entire extended name)
Address (Full address)

Just inner join the values

select * from Table1 inner join Table2 on Table1.Name = Table2.Name

Is that what you need?

that would be the easiest if table1.name and table2.name were identical in values. However, table1.name contains one word version of table2.name (for instance Microsoft in table1.name and Microsoft Corporation in table2.name). I need a way to search table2.name for each value in table1.name field.

Hi,

If I got ur question correctly, U r looking for matching words in table1 and table2 on a common colum.

U can use same Inner join with LIKE operator. lIKE OPERATOR CAN BE USED IN Inner Join colum itself or using a Where condition

SELECT TABLE1.NAME,TABLE2.NAME FROM TABLE1 INNER JOIN TABLE2
ON TABLE1.ID = TABLE2.ID
WHERE TABLE2.NAME LIKE '%'+ TABLE1.NAME + '%'
ORDER BY TABLE1.NAME

SELECT TABLE1.NAME,TABLE2.NAME FROM TABLE1 INNER JOIN TABLE2
ON TABLE1.ID = TABLE2.ID
WHERE TABLE2.ADDRESS LIKE '%'+ TABLE1.ADDRESS + '%'
ORDER BY TABLE1.ADDRESS

U can use '%' both sides to compare. There are some other ways e.g PatIndex, CharIndex etc.

I feel u should use the '%' both sides while comparing, It match the pattern.

Hope this helps.

B. Anant

Hey,

If using SQL Server, you may be able to use full-text indexing to compare versions of a word using FORMSOF(INFLECTIONAL())

Brian