Closest Match

[NICKTHEGREEK]

Is there anyway of creating a relationship between tables that will find a close match rather than an exact match to take input errors into consideration? The filed that may contain input errors is an imported text file so validation is not an option. Any ideas?

Thanks

 

[Guest_imported]

Hi Nick,

You could create a function that defines exactly what you mean by 'a close match' and then use that function in a query. For example, this function returns True if two strings are the same length and start with the same letter (e.g. "Tim" and "Tom&quot:

Public Function CloseEnough(str1 As String, str2 As String) As Boolean
CloseEnough = ((Len(str1) = Len(str2)) And Left(str1, 1) = Left(str2, 1))
End Function

You could then use this as follows:

SELECT tblX.NameFirst, tblY.NameFirst
FROM tblX, tblY
WHERE CloseEnough(tblX.NameFirst, tblY.NameFirst);

Best wishes,
mike

 

[gwinn7]

You can also look into using the "Like" clause, but this all depends on your criteria of what a "close match" is. Example:

"SELECT * FROM MyTable WHERE [MyField] Like 'Tr*';"

Using this in conjunction with Mike's suggestion could be handy. The example I made will look in "MyField" for any records beginning with a "Tr".

Gary
gwinn7

 

[MichaelRed]

It SOUNDS interesting. Gary and Mike have identified the core 'ISSUE'. What is the definition of " ... a close match ... ". If YOU can really define this, the functionality could be achieved. BUT - - - remmeber "beauty is in the eye of the beholder". what is a close match for you may not qualify for someone else, so YOU will need to give some pretty specific instructions as to what is close enough, and probably need some examples on both sides of the issue for each of the rules.

Another consderation here will be the level of complexity you are willing to 'pay for', as the process can get to be quite time consuming if there are a lot of 'rules' and lenghty fields.

You also state:

"The filed that may contain input errors is an imported text file so validation is not an option. But it doesn't really make sense to me. If the table is imported, then I would assume the info is an integral part of the db/table(s), so validation of the content should be an available tool. If you are strictly discussing input validation, then it MAY not be available, on the other hand, you certainly can (AND SHOULD) process the table/fields to check the contents and check/correct/flag records which have invalid/suspect information. You COULD also do this in the form of an append query. Make a new table with the same structure as your 'errant'/problem child thing. Add the validation rules/requirements. Append the old thing to the new thing. Ipso Presto! Validation!

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[BoxHead]

Nick, one of those "projects I'll get to one of these days" is a program that will compare a list of existing customers with a purchased list of existing businesses in the same geographic area.

The goal is to identify the businesses that are not currently customers. My list may show the company name as only an acronym and the purchased list could have the full name. One list may include ", Inc." while the other does not. "Company" vs. "CO", etc. It's similar to your situation, I think, in that I cannot count on exact matches.

I'm far from even beginning the project, however, I've given it some thought and a couple of ideas may be useful to you (...or not).

First, in my customer table, I'll add a second company name field, AliasCoName. When I identify a matching company on the purchased list, I'll insert its name in this field. The thinking is that I will be able to query the purchased list for companies that are NOT in my customer AliasCoName.

Second, I'll use qualifiers as Gary and Mike suggested to look for "something close". I plan on differentiating each "match" based on how exact and complete it is, but I have phone numbers and addresses to work with and that may be more than you need.

When "matches" are found, they will populate a listbox. I'll have the option of selecting one of the "matches" to be placed in the AliasCoName field (same business) or selecting a "No Matches Found" button and proceeding to the next record.

I realize this involves more manual decision-making than is normally desired, but I know of a single building that has 3 tenants: ZM Int'l, NM Int'l and ZIM Int'l. I can't see any way to dependably automate the decision in cases like this.

If you or others can devise a better way, I'm marking the thread and anxious to hear.

John