pattern matching in ms access query

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

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.

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