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

Posted on 2011-10-19
Last Modified: 2012-05-12
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

Question by:sheryd
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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:
    STATISTICSTABLE5.Title    KEYWORDS.KWDescription  KWCount
    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
    GROUP BY Title

    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

    LVL 1

    Author Comment

    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  
    LVL 1

    Author Comment

    Ooops. I meant to say
    Select * from StatisticsTable5 where KEYWORDS.KWDescription  is any part of (like) STATISTICSTABLE5.Title.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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.
    LVL 47

    Accepted Solution


    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
    INNER JOIN KeyWords
    ON STATISTICSTABLE5.Title LIKE "*" & KEYWORDS.KWDescription & "*"

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

    LVL 1

    Author Comment

    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
    LVL 1

    Author Closing Comment

    A bit hard to follow but I managed to get the results I wanted
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Sorry, I missed the part in your original post where you were doing "java programming", the code provided above was for a VBA solution.
    LVL 1

    Author Comment

    Actually its me who missed. I placed the question in the wrong zone, I hope a moderator can remove it from the Java zone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
    Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now