• 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

0
sheryd
Asked:
sheryd
  • 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:
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)
        rs.movenext
    Wend

    'cleanup
    rs.close
    set rs = nothing  

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

Open in new window

0
 
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  
0
 
sherydAuthor Commented:
Ooops. I meant to say
Select * from StatisticsTable5 where KEYWORDS.KWDescription  is any part of (like) STATISTICSTABLE5.Title.
0
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.
0
 
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
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.

0
 
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
0
 
sherydAuthor Commented:
A bit hard to follow but I managed to get the results I wanted
0
 
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.
0
 
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.
0
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