Partial Text Compare Across Two Tables

[mab9]

Hi,

I'm trying to compare partial text records across two tables.

The first table simply has a description, such as:
DescriptionHammer
Bone Saw
Power Saw

The second table is a list of terms and a category, such as:
Term, Category
Hammer, 1
Saw, 2

I'm looking to build a query will compare the two and assign each description a category based on the partial match. It seems like this should be fairly easy to do, but I'm struggling to find anything to point down that path.

Any ideas?

 

[Jon K]

Assuming the two tables are named tblDescription and tblCategory, try this non-equi join query.

SELECT [Description], [Term], [Category]
FROM [tblDescription] INNER JOIN [tblCategory] ON InStr([tblDescription].[Description], [tblCategory].[Term]);

To see if there are also descriptions that don't have an assigned category number, you can change INNER JOIN to LEFT JOIN.


Note: Non-equi join queries are non-updatable and cannot be displayed in query Design View.
.

 

[Bilbo_Baggins_Esq]

Couldn't you just add:
InStr([tblDescription].[Description], [tblCategory].[Term])>0
to the criteria for desired field?

And:
InStr([tblDescription].[Description], [tblCategory].[Term])=0
for the non-matched items?

This would allow to view in design view and i think it might even still be updateable???

 

[EMP]

'Adding InStr([tblDescription].[Description], [tblCategory].[Term])>0 to the criteria for desired field' uses the Where Clause to link the table instead of using a join. In Access, a join is more efficient than a link in the Where Clause. And using InStr() in the link still makes the query non-updatable.


Using 'InStr([tblDescription].[Description], [tblCategory].[Term])=0' cannot return the required non-matched records. If you add a Drill in the poster's data, such a query will return the results:

Description	Term	Category
Hammer		Saw	2
Bone Saw	Hammer	1
Power Saw	Hammer	1
Drill		Hammer	1
Drill		Saw	2

which are certainly meaningless,

whereas Jon's Left Join can correctly return the desired results and in one single query:

Description	Term	Category
Hammer		Hammer	1
Bone Saw	Saw	2
Power Saw	Saw	2
Drill

I think Jon's solution has been well thought out.

^

 

[Bilbo_Baggins_Esq]

I'm sorry, I should have added that I didn't mean to be doubting the original solution.
I'm always trying to learn new methods.

I thought I remembered using the Instr successfully as a criteria before in a similar circumstance, but perhaps I was mistaken.

sorry.

 

[mab9]

Assuming the two tables are named tblDescription and tblCategory, try this non-equi join query.

SELECT [Description], [Term], [Category]
FROM [tblDescription] INNER JOIN [tblCategory] ON InStr([tblDescription].[Description], [tblCategory].[Term]);

To see if there are also descriptions that don't have an assigned category number, you can change INNER JOIN to LEFT JOIN.


Note: Non-equi join queries are non-updatable and cannot be displayed in query Design View.
.

Jon this is perfect! Thanks a ton.