Search like from 2 tables (1 Viewer)

[Gismo]

Hi All,

is there any way to link 2 table text field to search for like description
table 1 could be something like - air conditioner installation
table 2 could be something like - S/N8754(32546) install air conditioner controller box

 

[Isaac]

Depending on the relationships you want, or you may not need any joins at all, it kind of depends on your data.
This query returns a record any time the Like condition is satisfied from the column in either table

 

[Ranman256]

make 3 queries:
Q1 = select 't1',ID,descr from T1 where [descr] like "*" & forms!myForm!txtFind & "*"
Q2 = select 't2',ID,descr from T2 where [descr] like "*" & forms!myForm!txtFind & "*"
Q3 = union query:
select * from Q1
union
select * from Q2


On a form , have a text box to type the search term: txtFind
the aferupdate opens Q3

sub txtFind_afterupdate()
if Not Isnull(txtFind) then docmd.openquery "Q3"
end sub

 

[CJ_London]

I'm making a different assumption for what you are looking for. I think you mean return all records in table 2 where there is a 'match' in table 1.

simplistically you can either use a cartesian query (no join) or a non standard join. In both cases use of Like with an initial * precludes the use of indexing so can be slow.

And as other posters have suggested you may also need to compare table 2 to table 1.

The problem with your example is 2 out of 3 words match 2 out of 6 in the other. So you would need to break each down into its individual words, then do a comparison of each of the 3 words in table 1 with each of the 6 words in table 2 and define a scoring system - return records where at least 2 words? 3 words? match. You need to consider whether order matters - does air conditioner 'match' conditioner air? also typo's and abbreviations - do you want 'aircon' to match 'air conditioner', do you want 'install' to match 'installation' etc.

 

[Gismo]

Depending on the relationships you want, or you may not need any joins at all, it kind of depends on your data.
This query returns a record any time the Like condition is satisfied from the column in either table

View attachment 83535

this is what I tried but with zero results

 

[CJ_London]

You would need to write your criteria as

Like "*" & [Operations] & "*"

However even with this correction, assuming your data is per your original example, it won't return anything

table 1 could be something like - air conditioner installation
table 2 could be something like - S/N8754(32546) install air conditioner controller box

only the 'air conditioner' part is in both records. So to get a return your like for table 1 would have to be

like "*" & left([Operations],instrrev([Operations]," ")-1 & "*"

and that is for just one record.

Suggest review post #4

 

[Isaac]

Yeah, I think I ended up interpreting the sparsely explained original post incorrectly. I took it there was a text value to be compared.
Looks like CJ _ London has you on the right track, thanks CJ.