MS Access Query to Get Count of Values based on values from 2nd Table

Posted on 2011-05-11
Last Modified: 2012-05-11
I have 2 Tables
1st Table has 1 Field in it - KEYWORDS, each value / row is unique
2nd Table has multiple fields, one of which is called KEYWORDS, which can contain multiple KEYWORDs, seperated by a comma.

What I want to do is the following :-
- For every record in Table 1 - I want to show how many times the value appears within Table2

The 2 Tables DO NOT have any common Fields which Link the 2 tables - other than the value in Table1 may appear within a Field (along with others) within Table2

eg. Table1 - Keyword1
      Table1 - Keyword2
      Table1 - Keyword3
      Table1 - Keyword4

      Table2 - Keyword3, Keyword4, Keyword1
      Table2 - Keyword1, Keyword2, Keyword3
      Table2 - Keyword1, Keyword3
      Table2 - Keyword2, Keyword3

Keyword1 = 3
Keyword2 = 2
Keyword3 = 3
Keyword4 = 1
Question by:pjelias
    LVL 40

    Accepted Solution

    try this.
      FROM (Table1 AS t1 
            LEFT JOIN Table2 AS t2 
              ON INSTR(1, t2.KEYWORDS, t1.KEYWORDS, 1) > 0) 

    Open in new window

    For Keyword3, count is 4.

    Author Closing Comment


    thank you - worked exactly as I needed.

    Greatly appreciated - especially the quick response

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 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

    18 Experts available now in Live!

    Get 1:1 Help Now