Approximate Matches

[bbraly52]

I have a table that has over 5.2 million rows, so I can't edit that a lot. Part of the table is a list of addresses. However, there isn't consistency to how the addresses are. I end up getting a list of addresses that need the information from the main table. Is there a way to create an approximate match. I am okay if I have to manipulate the query results.

Main Table - 123 N Main Street



Request Table - 123 Main St.

I am wanting to not have to do a manual query. Currently I am doing Like "123*" and Like "*Main*"... but my request table is getting to be over 100 rows.

[CJ_London]

Your example is fairly limited but I had to do something similar in the pharma industry

create an alias table

pk… alias….parent
1…..street…1
2……st……….1
3……pl….……3
4……place….3

etc

split The address into separate words and look up each word on the alias column and return the parent alias., at the same time you can regularise addresses with double spaces, remove dots etc

will take a bit of setting up and you may need to resolve conflicts such as st meaning street or perhaps is valid for St Peter’s

the rest depends on how your data is organised but ideally you need to split it into a number of columns such as name/number, street name, street type, town, postcode

if you are only worried about street type (street, road, place, square etc) in your query you would left join your address table to your alias table on streettype=alias, which in turn would be left joined to itself (aliased as say aliasP) on parent=pk

and in your query you would have

WHERE nz(aliasP.alias,address.streettype) = [enter street type] and streetname like "*" & [enter street name] & "*" etc

For your search string, you can train users to use the + character to separate the different elements and to use the * when required. Or use separate controls for each element

123+main+st
123+*main+st


you can the use the split function to split the string on the + and add the elements to the criteria accordingly

As an aside I also train users to use the / char to indicate OR and the - to indicate between as well as using the * when required - but that may not be appropriate for addresses

123/125+main+st

might return
123 Main St
123 Main Street
125 Main Street
125 Main St

123-125+main+st
might return
123 Main St
124 Main Street
125 Main Street

*+main+st
will return all main st and main street records

which again you can then split to create your criteria with Or's



edit2:

you say Currently I am doing Like "123*" and Like "*Main*"

you could just combine as Like "123*main*" i.e. train users in how to use the *