Compare 2 Fields and report on the accuracy

  • Thread starter Thread starter bluestar
  • Start date Start date
B

bluestar

I've been given a huge task of checking thousands of serial number
against a source report - So far I've been doing my best, quering o
exact matches and then on part of the field. - but this is so tim
consuming as I am seeing loads of typos like S & 5, O & 0 (zeros) etc.

As I have all the information in two tables, I was wondering i
anything could be done to link the two tables and have an outpu
created that shows all the serial numbers that match on the mos
charactors (starting with the match going from right to the left i
possible) e.g J714FM712345 in one column and J613FM712345 in the othe
with an expression that says matches on last 8.

Also,

Is it possible to do something that counts the source string e.
J714FM712345 and compares it to another field say J613FM712345 and the
reports back to say that "10 charactors match exactly and in the sam
position - but 2 don't!"

Any help would be much appreciated - I;ve gone through a search as bes
i can and can see things that vcome close to both but nothing seems t
match exactly or works so far.
 
bluestar said:
I've been given a huge task of checking thousands of serial numbers
against a source report - So far I've been doing my best, quering on
exact matches and then on part of the field. - but this is so time
consuming as I am seeing loads of typos like S & 5, O & 0 (zeros) etc.
Click to expand...

Awful. Glad *you* got the task, not *me* :-)
As I have all the information in two tables, I was wondering if
anything could be done to link the two tables and have an output
created that shows all the serial numbers that match on the most
charactors (starting with the match going from right to the left if
possible) e.g J714FM712345 in one column and J613FM712345 in the other
with an expression that says matches on last 8.
Click to expand...

I can write a function that will return the match number; but I am
uncertain as on how to *link* tables based on that information. Maybe
that sorts itself as the function is available.

I am assuming for now the strings have equal length. If not, extra
measures are required.

function rightMatch(c1 as string, c2 as string)as long
dim i as long
i = len(c1)
do while mid(c1,i,1)=mid(c2,i,1)
i=i-1
if i=0 then exit do'precaution if exact match
loop
rightMatch = len(c1)-i
end function
Also,

Is it possible to do something that counts the source string e.g
J714FM712345 and compares it to another field say J613FM712345 and then
reports back to say that "10 charactors match exactly and in the same
position - but 2 don't!"
Click to expand...

I don't feel like playing MasterMind with these codes--I presume the
match is positional (i.e. the 3 from 613 should not match the 3 in
714FM712345). That way, such a test looks like

function countMatch(c1 as string, c2 as string) as long
dim nRes as long
dim i as long
for i=1 to len(c1)
if mid(c1,i,1)=mid(c2,i,1) then nres=nres+1
next
countMatch=nres
end function

the non-matching amount is of course the string length minus the match
count.
 
Back
Top