pattern matching in ms access query

[vijaykaali811]

i have table T1 with  query, name 

T2  table pattern , category

Query  can be anything 

t2 table 

pattern2                     category

connection*failure*    connectivity 

power*failure*             power issue 

i am trying like 

select t1.query, t2.patter,t2.category  from t1,t2 where  query like "*"&t2.pattern2&"*"

but in MS access it says join does not suppport expression  . how to achieve this. 

[Avyaktha]

hi vijaykaali811 

In MS Access, you can't use a pattern match (LIKE "*"&...&"*" ) as part of a join. Access doesn't allow expressions like that in joins.

But you can still do what you want by writing the query like this:

SELECT t1.query, t2.pattern2, t2.category
FROM t1, t2
WHERE t1.query LIKE "*" & t2.pattern2 & "*";

This is how it will work : 
It compares every row in t1 with every row in t2, and filters them where the pattern matches.

BUT If t2.pattern2 has * inside (like connection*failure*), that won’t work as you expect.
Access uses * as a wildcard, not as a separator. So instead you can store just simple text like connection or failure

Or split pattern2 into multiple columns if you want to check multiple words.

Thank you 
Avyaktha 

[Shahid12523]

In MS Access, you can’t use LIKE in the join.

Do this instead:

SELECT t1.query, t2.pattern2, t2.category
FROM t1, t2
WHERE t1.query LIKE "*" & t2.pattern2 & "*";

👉 Put LIKE in the WHERE clause, not the join.

[v-lgarikapat]

Hi @vijaykaali811 ,

Thanks for reaching out to the Microsoft fabric community forum.

Thanks for sharing your question here Since your issue seems to be related specifically to pattern matching in MS Access queries, I’d recommend also posting this thread in the Microsoft Access Community. There are many experienced Access developers and experts there who are more focused on Access-specific syntax and behaviour they may be able to offer more targeted assistance.

Database Software and Applications | Microsoft Access

Best Regards,

Lakshmi Narayana