All records in a List_Table as the criteria for query of a Main_Ta

  • Thread starter Thread starter PXK
  • Start date Start date
P

PXK

I am getting exact matches to the List_Table using the IN statement, but...
Consider a customer is inputting a "where did you hear about us" answer in
to a Main_Table. They may type "Yahoo", or "Yahoo search engine", or "Yahoo
online" etc. The next person may type "Magazine A", or "Magazine A and Yahoo"
etc.

I need to return all of the records in the Main_Table column, that match or
have as part of them, any of the entries in the List_Table. If someone can
roll in a piece to handle misspellings which may occur on the customer input
side, that would be amaizing. Things like Yaho, Yaahoo and the like.
 
PXK said:
I am getting exact matches to the List_Table using the IN statement, but...
Consider a customer is inputting a "where did you hear about us" answer in
to a Main_Table. They may type "Yahoo", or "Yahoo search engine", or "Yahoo
online" etc. The next person may type "Magazine A", or "Magazine A and Yahoo"
etc.

I need to return all of the records in the Main_Table column, that match or
have as part of them, any of the entries in the List_Table. If someone can
roll in a piece to handle misspellings which may occur on the customer input
side, that would be amaizing. Things like Yaho, Yaahoo and the like.
Click to expand...


You can get an exact match by using a join instead of IN:

SELECT M.*, H.Place
FROM MainTable As M INNER JOIN HearAboutUs As H
ON M.WhereHeard = H.Place

Then you can modify that to find matches within the field by
using a non-equi join
ON M.WhereHeard Like "*" & H.Place & "*"

Note that you can not specify a non-equi join in the query
design grid. You must use SQL view to create/modify the
query.

You can never get all variations of typos/misspellings. One
less than optimal, but relatively easy approach would be to
add bad names to your list table.

A better approach would be to use a SoundX value (Google it)
in your list table. But this would also require you to
create a function to parse out each word in the main table
field and check it against the list table. This would be a
lot of work and would probably suffer from poor performance.
 
Can not handle misspellings in your Main_Table.
As for criteria for the Main_Table use Like "*" & [List_Table] & "*"
 
Thanks for the prompt responce, I got a Enter Parameter Value for
[List_Table] when I ran this. As far as I know I can't join the two tables
as don't realy have matching fiels (when I tried this I got hundred some
thousand results, I think its called a Cartesian something rather result). I
also tried your suggestion in the IN statement, but only got exact matches
back. Maybe I'm not putting it in the right place.

SELECT [Main_Table].[ContactSource]
FROM [Main_Table]
WHERE ((([Main_Table].[ContactSource]) In (Select
  • FROM [List_Table]
    WHERE [Main_Table].[ContactSource] Like"*" & [List_Table].
    • & "*")));

      --
      Thank you for your help


      KARL DEWEY said:
      Can not handle misspellings in your Main_Table.
      As for criteria for the Main_Table use Like "*" & [List_Table] & "*"

      --
      KARL DEWEY
      Build a little - Test a little


      PXK said:
      I am getting exact matches to the List_Table using the IN statement, but...
      Consider a customer is inputting a "where did you hear about us" answer in
      to a Main_Table. They may type "Yahoo", or "Yahoo search engine", or "Yahoo
      online" etc. The next person may type "Magazine A", or "Magazine A and Yahoo"
      etc.

      I need to return all of the records in the Main_Table column, that match or
      have as part of them, any of the entries in the List_Table. If someone can
      roll in a piece to handle misspellings which may occur on the customer input
      side, that would be amaizing. Things like Yaho, Yaahoo and the like.
      Click to expand...
      Click to expand...
 
Back
Top