• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

MS access query to select values that exist in another table but not exact match

I have two tables. Access 2007
1. KEYWORDS has one field named KWDescription with values (orange, apple, mango, tomato)
2. STATISTICSTABLE5 has several fieds that some of which I would like to display.  One of the fields is called Title. Fields in Title have values like.  
This is an apple.
I sold a Mango.
I ate a tomato.

i.e some values in Title have some content from KWDescription
I would like to use a query to select all rows in STATISTICSTABLE5 for each row that has a keyword(KWDescription) found is found in the Title.

Sample resultrequired:
STATISTICSTABLE5.Title    KEYWORDS.KWDescription  Count of Keyword
This is an apple.                       Apple                                           1
I sold a Mango and Orange     Mango, Orange                         2
I ate a tomato                          Tomato                                          1

I have tried this but am not getting any meaningful results
Any help is appreciated.
SELECT Exists (Select * from Keywords where Keywords.KWDescription = StatisticsTable5.Title) AS Expr1, StatisticsTable5.Title
FROM StatisticsTable5
WHERE (((Exists (Select * from Keywords where Keywords.KWDescription = StatisticsTable5.Title))<>False))

Open in new window

  • 5
  • 4
1 Solution
Dale FyeCommented:
I would start with a query (qry_Title_KeyWords) that looks like this:

SELECT ST5.Title, KW.KWDDescription, (Len(ST5.Title) - Len(Replace(ST5.Title, KW.KWDDescription, "")))/LEN(KW.KWDDescription) as KWCount
FROM StatisticsTable5 as ST5, KeyWords as KW
WHERE instr(ST5.Title, KW.KWDDescription) > 0

This should get you:
This is an apple.                       Apple                                        1
I sold a Mango and Orange     Mango                                       1
I sold a Mango and Orange     Orange                                      1
I ate a tomato                          Tomato                                     1

From there, you could do a concatenation of the KWDescription column, something like:

SELECT Title, fnConcat("KWDescription", "qry_Title_KeyWords", "[Title] = " & chr$(34) & qry_Title_KeyWords.Title & chr$(34)) as KeyWords, SUM(KWCount) as KeyWordCount
FROM qry_Title_KeyWords

Public Function fnConcat(FieldName as string, Tablename as string, Optional Criteria as string) As String

    Dim strSQL as string
    Dim rs as DAO.Recordset
    Dim strConcat as string

    strSQL = "SELECT [" & Fieldname & "] FROM [" & TableName & "]"
    strSQL = replace(replace(strSQL, "[[", "["), "]]", "]")
    if Criteria <> "" then strSQL = strSQL & " WHERE " & Criteria

    set rs = currentdb.openrecordset(strSQL,,dbfailonerror)
    While not rs.eof
        strConcat = strConcat & ", " & rs(0)

    set rs = nothing  

    'strip the leading comma and space from strConcat
    fnConcat = mid(strConcat,3)
End Function

Open in new window

sherydAuthor Commented:
This looks like the long way home. any way to do this withoud using code ?  I am trying to use the "Like" function e.g.
Select * from StatisticsTable5 where KEYWORDS.KWDescription  is found in (like) KEYWORDS.KWDescription  
sherydAuthor Commented:
Ooops. I meant to say
Select * from StatisticsTable5 where KEYWORDS.KWDescription  is any part of (like) STATISTICSTABLE5.Title.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Dale FyeCommented:
The issue is that you want to concatenate two values from your KeyWords table, and there is no easy way to do that without using a function call like fnConcat.
Dale FyeCommented:

In your example, the query would look like:

Select * from StatisticsTable5, KeyWords where STATISTICSTABLE5.Title LIKE "*" & KEYWORDS.KWDescription & "*"

But this will just get you something that looks similar to my first query, without the number of occurances of KWDescription that is in each [Title].

You could also use:

Select *
FROM StatisticsTable5

but you cannot view this query in the query grid because of the use of "Like" in the join.

sherydAuthor Commented:
The first solution seems to be ok but I am getting an error on the line "Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)"

"Run-Time error '3075'
Syntax error(missing operator) in query expression.."

I have to click "End" button  several times before I get results. Results seem to be ok. Still checking
sherydAuthor Commented:
A bit hard to follow but I managed to get the results I wanted
Dale FyeCommented:
Sorry, I missed the part in your original post where you were doing "java programming", the code provided above was for a VBA solution.
sherydAuthor Commented:
Actually its me who missed. I placed the question in the wrong zone, I hope a moderator can remove it from the Java zone.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now