Joining tables based on strings contained within field

[chatshah]

I have two tables Main and Keyword. Main has a Description field. Keyword has
Keyword and Key field. I want to be able to create a query that returns Key
from Keyword table if Description contains the string in Keyword field. For
example:

Keyword Table
Keyword Key
, LTN, Latin
CYR Cyrillic

Main Table
Description
ASSY, MGN, LTN, TEMP
ASSY, BLU, LTN, TEMP
ASSY, PNK TEMP, LTN,
ASSY, CYR, GRN, TEMP

Result Table
Description Key
ASSY, MGN, LTN, TEMP Latin
ASSY, BLU, LTN, TEMP Latin
ASSY, PNK TEMP, LTN, Latin
ASSY, CYR, GRN, TEMP Cyrillic

How do I set up my query to be able to do this? Thanks

 

[Jerry Whittle]

SELECT Main.Description, Keyword.Key
FROM Main, Keyword
WHERE (((Main.Description) Like "*" & [Keyword].[Keyword] & "*"));

However there are many things that could go wrong with the above. You really
need another table with each of the Descriptions broken into their own record.

 

[chatshah]

Sorry, I am new to access. How should I do it? Can I create a new query from
the Keyword table showing only the Key column?

Also, I want to create the query in such a way that if there are is no match
in the Keyword table it still shows the records,

e.g. if Description has string ASSY, MGN, TEMP then it will still show up in
the result table

SELECT Main.Description, Keyword.Key
FROM Main, Keyword
WHERE (((Main.Description) Like "*" & [Keyword].[Keyword] & "*"));

However there are many things that could go wrong with the above. You really
need another table with each of the Descriptions broken into their own record.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have two tables Main and Keyword. Main has a Description field. Keyword has
Keyword and Key field. I want to be able to create a query that returns Key
from Keyword table if Description contains the string in Keyword field. For
example:

Keyword Table
Keyword Key
, LTN, Latin
CYR Cyrillic

Main Table
Description
ASSY, MGN, LTN, TEMP
ASSY, BLU, LTN, TEMP
ASSY, PNK TEMP, LTN,
ASSY, CYR, GRN, TEMP

Result Table
Description Key
ASSY, MGN, LTN, TEMP Latin
ASSY, BLU, LTN, TEMP Latin
ASSY, PNK TEMP, LTN, Latin
ASSY, CYR, GRN, TEMP Cyrillic

How do I set up my query to be able to do this? Thanks

 

[Jerry Whittle]

Open up a new query based on the Main table. Go to View, SQL View. Copy and
paste in the SQL statement below after removing what already there. Make sure
that all the field and table names are correct.

SELECT Main.Description, Keyword.Key
FROM Main, Keyword
WHERE (((Main.Description) Like "*" & [Keyword].[Keyword] & "*"))
UNION ALL
SELECT Main.Description, null
FROM Main
WHERE Main.Description not in(SELECT Main.Description
FROM Main, Keyword
WHERE (((Main.Description) Like "*" & [Keyword].[Keyword] & "*")));

Don't even ask how it works as it's complicated and I don't know how to
explain it! The cause of this complication is the incorrect structure of your
Description field. It should be in another table. Then the SQL statement
would be much simpler and even something that could be created in the QBE
grid of the Query Design view.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry, I am new to access. How should I do it? Can I create a new query from
the Keyword table showing only the Key column?

Also, I want to create the query in such a way that if there are is no match
in the Keyword table it still shows the records,

e.g. if Description has string ASSY, MGN, TEMP then it will still show up in
the result table

SELECT Main.Description, Keyword.Key
FROM Main, Keyword
WHERE (((Main.Description) Like "*" & [Keyword].[Keyword] & "*"));

However there are many things that could go wrong with the above. You really
need another table with each of the Descriptions broken into their own record.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have two tables Main and Keyword. Main has a Description field. Keyword has
Keyword and Key field. I want to be able to create a query that returns Key
from Keyword table if Description contains the string in Keyword field. For
example:

Keyword Table
Keyword Key
, LTN, Latin
CYR Cyrillic

Main Table
Description
ASSY, MGN, LTN, TEMP
ASSY, BLU, LTN, TEMP
ASSY, PNK TEMP, LTN,
ASSY, CYR, GRN, TEMP

Result Table
Description Key
ASSY, MGN, LTN, TEMP Latin
ASSY, BLU, LTN, TEMP Latin
ASSY, PNK TEMP, LTN, Latin
ASSY, CYR, GRN, TEMP Cyrillic

How do I set up my query to be able to do this? Thanks