closest match using Visual basic access.. help will be rewarded

[tseng]

I need to create a program which will perform closest match on my database.
If anyone is willing to help i will be happy to give them a little something.
please email me or post back.
I have loads of program which you may want me to post to you.
photoshop cs 8
corel draw graphics suite 10
norton 2005
Ad aware SE premiere
and many others.
Anyone intersted in helping me, please...

 

[The_Doc_Man]

"Closest Match" implies certain types of "fuzzy logic" operations. Not that you couldn't do this in VBA, but it would not necessarily be fast. Remember, VBA is only pseudo-compiled, unlike say, VB6 or C++. If you are doing text-oriented measures of closeness, it will be even slower since you have to decompose the strings into pieces-parts.

OK, having put that warning aside, the next step: Before you or anyone else can answer this question, you must FIRST define what constitutes a match and what it means to be close. Which means you must define a "distance" function. ('cause obviously, "closest" means "least distant", right?) For instance...

If you are comparing numbers, is 100255 a distance of 1 from 100256? This is a "trivial" measure of distance, meaning "difference" when talking about searching for closest numbers.

Now, if you are talking REAL numbers, is 100.255 a distance 0.001 from 100.256, or is it a distance of 1 based on the difference in the representation of the numbers?

If you are comparing strings, is "ABC" a distance of 1 from "ABD" ? and "ABB" a distance of 2 from "ABD" ? This is one possible measure of distance, meaning an assigned measure of distance per element of the string when compared character by character.

This might sound trivial, but it isn't... because the NEXT step is to define distances when things AREN'T so close together.

For instance, what is the distance between 100255 and 10255? (Not the same order of magnitude even though they use the same digits in their representation.)

What is the distance between "ABCD" and "ABD"? Or between "ABC" and "ABCD"? (Strings are not the same length, so what positional weight, if any, applies to their difference?)

What is the distance between "BBC" and "ABC"? (When the difference appears early rather than late in same-length strings? WHY is this a difference of only 1 or a difference of some other number? I.e. what rule applies to this kind of difference?)

What is the distance between "ABCD" and "BCD"? (One string is a formal subset of another. What significance is implied by the difference?)

What is the difference between "ZYX" and "ABC"? (Same length but not even sorted in the same order, if that concept is even applicable.)

OK, I've beaten that horse to death... But then, it wouldn't be the first time I was accused of being a sadistic, sodomistic necrophile. (With apologies to Woody Allen and What's Up Tiger Lily.)

Back to the serious response. Once you have DEFINED what you mean by "Closest" then you can define a function to MEASURE / COMPUTE / REPORT closeness. This function can then be made into a public function so you can use it in a query.

Next,... how close is close enough?

If you order by closeness, do you have a threshold? Like, it ain't close unless it is within, say, 100 "distance units" in your closeness function? Or do you just want to see the closest, 5 closest, 10 closest, etc. regardless of just how close they are?

Will the distance always be measured between strings? Or can you seek the distance between numbers sometimes?

See, this is why when someone comes up with really spiffy search algorithms they become potential gazillionaires like the folks at Google recently did. So now, a serious question that brings us back into reality...

If I had such a good "closest-match" algorithm, why in Hell would I give it to you for a lousy program or two? And if I weren't such a hot-shot as to have a very good algorithm, why would you want to use whatever method I gave you?

Think about what you are asking before you ask it.

 

[tseng]

Well it does seem quite easy but i do not know where to start.

Well i have a table called [turkeys] and a table called [invoice].
I need to match [turkey weight 1] in the [invoice] table with the
closest numerical value in[turkey weight] in the [turkeys] table.

There will be a maximum of 40 records in the [turkey weights] so
speed is not a problem.Would i then be easier to use the vlookup
function instead of using VBA.

This value then has to be put into the [invoice] table in the
[actual weight 1] column.

Then the weight in the [turkeys] has to be deleted. ** i think i can do this part**

Please help me

 

[FoFa]

With 40 rows, it is not that big of a deal. Youo could create multi-queries to do it, or a function.
A function would take your Invoice Weight as a parameter.
Read throught each row of the Turkey table using this calculation,
ABS(inv_Weight - Turkey_Weight)
Hold the primary key and results of the first calculation.
Read next row
do Calculation
If result of calculation is less than last one, hold new primary key of turkey table
When out of rows, return primary to turkey table (should be weight closest to Inv Weight).

Well should work

 

[tseng]

Oh yeah.
Never thought of that, thankyou.
Its the same as a loop really.
I will give this option a go.

 

[tseng]

i didn't get what the abs was for.
I have made a little program now but i was wondering how to configure this for my access tables as this is the first time i have used vb for access.
In the past i have just used VB for little programs so i didn't really need to know naything about access.
Thankyou for your help.

Function find_match()
Dim weight_given As Integer
Dim weighttemp As Integer
weight_given = Val(text1.Text)
turkey_weight= Val(text2.text)
Do
weight_given -Turkey_Weight
result = weightless
weighttemp = weightless \ 1
weight_given -turkey_weight2
If result > weighttemp Then Loop
If result < weightemp Then weighttemp = result
Loop Until weighttemp = 0
'i will ensure there is a value of 0 at the bottom of table
weighttemp = actual_weight
End Function