How do I Match Partial Keywords from "Table_GEO" with "Table_Main"?

[chopin]

I am using Access 2007/2010. I have a large list of data (I will call it "Main), in which phrases contain a city or state, plus a keyword. I then have a table called "GEO" which will contain a static list of cities and states for matching purposes. My goal is to eliminate the "keyword" in the string in "Main", but leave the city/state behind. Examples:

• New Jersey Personal Injury
• Pennsylvania Medical Malpractice
• Med Mal in New Jersey
• PI attorney in Kentucky

I want to convert the above list to the following:

• New Jersey
• Pennsylvania
• New Jersey
• Kentucky

Again, I have a list of all of the cities/states on hand in Table GEO, for the keyword recognition to take place. Any help would be appreciated. Thank you.

[nico5038]

A special JOIN can solve this for you.

Just create a query joining rows when the Description is LIKE the city/state from your table. This would look like:

SELECT tblMain.Description, Nz([CityState],"<Not found>") AS [Found]
FROM tblMain LEFT JOIN tblCity ON tblMain.Description like "*" & tblCity.CityState & "*";

Guess the fieldnames need to be changed, but I guess you get the idea.

When the query is working, change it into a MakeTable query to get the final result.

Basically

[chopin]

I'm not sure if a query would work here. I should have specified, there is not a one to one match. For example, I will have over 100,000 keywords to match against 5000+ or so cities in the US. This is why I am not sure if a join will work since there is really no way to join the two data sets one to one. I am thinking that the only way I can accomplish what I want is by using DAO, but I am not so sure? Maybe I am wrong? Do the data sets have to have identical rows to use a join query?

[nico5038]

It will work as you need, number of rows don't matter.
Just check the way I constructed the JOIN. It's no 1:1, but a conditional one.

Mismatches will show because of the NZ() function like "<Not found>"

The only problem will be an overlap of city/state names like "New York" and "York", these will trigger the JOIN twice and result into two hits.
Having a unique ID in the "Main" table will enable you to solve this with a group by query.

Just try.

Nic;o)

[chopin]

Ok you were right, this does work very nicely. But like you said, there is an overlapping problem. Since I am using state abbreviations, phrases such as "personal injury in New Jersey" could spit out "AL". I'm not really understanding how a unique identifier could help in this case? You said

The only problem will be an overlap of city/state names like "New York" and "York", these will trigger the JOIN twice and result into two hits.

Sure, this could help to resolve additional queries, however it won't help with accuracy. For example, how would the query know to choose New Jersey vs AL? What if it chooses AL first? Then I am out of luck.

If this does help, the way I was able to get this to work in excel is as follows:

=IF(ISNA(LOOKUP(9999,SEARCH(" "&City&" "," "&G2&" "),City)),"Statewide",LOOKUP(9999,SEARCH(" "&City&" "," "&G2&" "),City))

"City" represents the array of cities, and G2 is the target keyword string. To account for exact matching, I used the following queries

" "&City&" "
" "&G2&" "

I'm not sure how to translate this into Access?

[chopin]

Ok, after doing some more research, I came up with the following possibilities:

like "* " & geo.CityState
like geo.CityState & " *"
like "* " & geoCityState & " *"

I can "sorta" get all functionality working now, but there are still problems with queries like "york, and new york". Anyway, I don't know how to combine the above queries into one query. I tried this:

 ...like "* " & geo.CityState or "* " & geoCityState & " *" or geo.CityState & " *";

But when I use the "or" condition, I continue to get errors. So technically, I can create three queries and merge all 3 results into one, but there must be some way to combine all statements into one...

[nico5038]

OK, to start with you need to use the:
like "*" & tblCity.CitySta te & "*"
to make sure all hits are found. This solves all three combinations you were experimenting with.

Best to solve this in stages:
1) Extract the matches and the length of the CityState field
2) Use the previous result to select the max length
3) Join queries from stage 1 and stage 2 by the Description and the (Max) length

Stage 1 give a query like:

SELECT tblMain.ID, tblMain.Description, Nz([CityState],"<Not found>") AS [Found], len(tblCity.CityState) AS CityStateLen
FROM tblMain LEFT JOIN tblCity ON tblMain.Description like "*" & tblCity.CityState & "*"

Stage 2 looks like:

SELECT ID, Description, Max(CityStateLen)
FROM qryStage1
GROUP BY tblMain.ID, tblMain.Description

Stage 3 looks like:

SELECT qryStage1.Description, qryStage1.Found, qryStage1.CityStateLen
FROM qryStage1 INNER JOIN qryStage2
ON (qryStage1.CityStateLen = qryStage2.MaxOfCityStateLen) AND (qryStage1.Description = qryStage2.Description);

Now missing matches will be dropped, so it's advisable to inspect those first. My experience with text fields is that not every user is entering City names as accurate as you would wish....

Nic;o)

[chopin]

Everything above works great, thanks so much. There is just one tiny problem now. Very short strings, such as state abbreviations still pose a problem with this code:

like "*" & tblCity.CitySta te & "*"

Example: "personal injury" spits out "al" for alabama. I think the way to eliminate this problem is by including spaces in the matching queries like I had above. Thoughts?

[nico5038]

Guess you stumbled upon the end of automating the solution of your problem.
In situations like this I revert to the manual check and update of the source text to make it fit. There won't be a 100% proof way to detect all automatically. That's also why I mentioned typo's in City Names. Only human eyes (and intelligence) can solve all.

Going further technically will probably cost more time than manually checking the odd matches (or mismatches)

Nic;o)

[chopin]

Actually I figured out the problem, I needed this code to give me what I need:

like "* " & geo.State or  main.keywords like "* " & geo.State & " *" or main.keywords like geo.State & " *";

Now missing matches will be dropped, so it's advisable to inspect those first.

Actually, is there a way to keep missing matches in this data set? A missing term in this case indicates something important.

[nico5038]

Yes, by forcing a value for the length when no city is found.

Nic;o)

[chopin]

That's what I thought, but when I tried it, the numbers got all screwy. Here is what I tried, for the first query:

...nz(geo.State,"national") AS [found], nz(len(geo.State),1) AS StateLen

So 1's are being placed where blanks are, however when I run query 2, the max lengths are screwed up. The problem is with the nz function, because as soon as I took out the nz function, the max lengths were fine again for query 2.

[chopin]

Since this problem is very important for me to fix, I attached my testing database file. All you have to do is run UpdateState1, then UpdateState2 (well, of course you will know this :)). You will see how UpdateState2 does not match UpdateState1 in State Length when I force values into the null fields using the nz function in UpdateState1. But when I erase the nz function in UpdateState1, values are perfectly normal. I'm not sure if this is an Access bug, or maybe I am just not seeing something here that the nz function is doing?

[nico5038]

You need to use an IIF inquery UpdateState1 like:

SELECT zoomrank.ID, zoomrank.keywords, nz(geo.State,"national") AS [found], IIF(isnull(geo.State),999,len(geo.State)) AS StateLen
FROM zoomrank LEFT JOIN geo ON zoomrank.keywords like "* " & geo.State or  zoomrank.keywords like "* " & geo.State & " *" or zoomrank.keywords like geo.State & " *";

Nic;o)

[NeoPa]

You may get away with adding the surrounding spaces manually and doing just the single match :

SELECT zoomrank.ID
     , zoomrank.keywords
     , Nz(geo.State,'National') AS [Found]
     , Len(Nz(geo.State, 'X')) AS StateLen

FROM   zoomrank LEFT JOIN
       geo
  ON   ' ' & zoomrank.keywords & ' ' Like '* ' & geo.State & ' *'

(I think.)