RE: Joining tables based on strings contained within field

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.

"chatshah" wrote:

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

"Jerry Whittle" wrote:

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.


"chatshah" wrote:

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

.

Relevant Pages

  • RE: Search Form - Take 2
    ... The search function is working like a charm when I enter a keyword in the ... as its due to the fact that the columns in master allow Nulls. ... Recommendations and Accomplishments tables an extra ... Create a query in which the Master table is joined to each of the other ...
    (microsoft.public.access.gettingstarted)
  • RE: Assigning unique identifier to unique authors
    ... The SQL statement would look something like below. ... Books to your actual table name. ... Jerry Whittle, Microsoft Access MVP ... Create a query based on just the FirstName and LastName fields. ...
    (microsoft.public.access.queries)
  • RE: Query criteria repeating
    ... The SQL statement doesn't define the parameter. ... Jerry Whittle, Microsoft Access MVP ... same criteria twice before the query runs. ...
    (microsoft.public.access.queries)
  • RE: how do I update multiple queries at once?
    ... SQL statement: ... Jerry Whittle, Microsoft Access MVP ... I assume that F1 and F2 are different fish ID's? ... Something like the query below can find ...
    (microsoft.public.access.gettingstarted)
  • Re: On form, text box is limited to 255 characters!
    ... but I'm not doing this in a query through Access. ... Please open the form in design view, open the property sheet of the ... SQL statement -- that uses the DISTINCT keyword, ...
    (microsoft.public.access.forms)