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

Posted on 2011-10-19
Medium Priority
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
  • 5
  • 4
LVL 49

Expert Comment

by:Dale Fye
ID: 36992664
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


Author Comment

ID: 36993160
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  

Author Comment

ID: 36993183
Ooops. I meant to say
Select * from StatisticsTable5 where KEYWORDS.KWDescription  is any part of (like) STATISTICSTABLE5.Title.
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 49

Expert Comment

by:Dale Fye
ID: 36993203
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 49

Accepted Solution

Dale Fye earned 450 total points
ID: 36993340

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.


Author Comment

ID: 36994252
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

Author Closing Comment

ID: 36999508
A bit hard to follow but I managed to get the results I wanted
LVL 49

Expert Comment

by:Dale Fye
ID: 36999598
Sorry, I missed the part in your original post where you were doing "java programming", the code provided above was for a VBA solution.

Author Comment

ID: 36999923
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

830 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