Matching words partially

[camedeiros]

I have to match two tables and I'm having a problem. Let me explain with an example. I am using address as a way opf joining them.

One table has 6549 N High St as the address for customer A. The other has 6549 N. High Street, with the "." and the whole word street. Is there a way to match them in a way that Access only captures the number (6549) and the street name (High)? Just as if I told Access to match any record for which 6549, and High was included, whatever position in the word it was.

Further, there are a bunch of records, so I think that a criteria specifying the specific addresses would not work because it would take me forever. I would need something that said: "if sentence X and Y are included in this word, then use it as a match.

Any ideas??

Thanks

 

[The_Doc_Man]

This sort of thing is possible but you are running into a potential headache, and the more "elements" you seek in the field, the more work you give yourself.

One approach would use the Like operator to generate lists of things that matched one or more of your search items. But it would not be very flexible.

In theory, if you use a Like selection criterion, you could generate a list of records that contained the fragment you supplied.

SELECT RecNo, "1" As Score WHERE AddrField LIKE "*High*"
UNION
SELECT RecNo, "1" As Score WHERE AddrField LIKE "*6549*"
UNION
SELECT RecNo, "1" As Score WHERE AddrField LIKE "*N*";

Then run a summation query on this union query, group by RecNo, and show the sum of the scores. The records with a score of 3 contain all of your search elements. There are also tricks to use regarding order of appearance, but at least in general, this method would be one possible starting place.

The part that isn't so flexible is that you would need to rewrite the UNION query to add a fourth criterion.

Another way to do this isn't shareable too well, but you could create a table that contained the record numbers deposited by a series of Append queries that appended the record number if and only if the LIKE operator matched the address field. Then group-by (and count the repetitions of) the record number. Again, biggest score = biggest match, and doing it this way, you could perhaps turn the search into a parameter query. (Look that up.)

However, in general, partial text matching requires you to decide ahead of time what tests you are willing to perform, knowing that you have lots of fields to search and that anything you do will probably take a while. It is a trade-off between more accurate vs faster searches.