Compare Fields in 2 tables : Find same Words in another field and score result

Access 2003
I want to compare text(alpha words) in table(tblData-fldDescription) to another table(tblSku-DESC) and score the finds
in a field called "fldScore"
tblData: field I need to use is fldDescription
tblSku: field I need to use is DESC

What To Do:
Loop through each record in tblData(fldDescription), collect an array of words.
then search in each record in tblSku(DESC)
as it finds words in tblSku, create a score based on the numbers of words found FOR each record.


Example data in tblData
(fldDescription)                                                                            (fldScore)
MOTOR, 1/2HP  CAPACITOR START 3 PHASE TEFC          

Array of words would be   MOTOR,CAPACITOR,START,PHASE,TEFC

Example data  tblSku(DESC)
SUMP PUMP MOTOR, 3 PHASE
MOTOR CAPACITOR DUTY, 1/2 HP 3 PHASE TEFC
LAMP, WARM LIGHT            <----  THIS LINE WILL NOT BE RETURNED BECAUSE NO WORDS MATCH


Lets give 5 points for each match


The sql  would return:
flddescription                               DESC                                  FLDSCORE
MOTOR, 1/2HP  CAPACITOR START 3 PHASE TEFC   SUMP PUMP MOTOR, 3 PHASE              10 (ONLY 2 WORDS FOUND)
MOTOR, 1/2HP  CAPACITOR START 3 PHASE TEFC   MOTOR CAPACITOR, 1/2 HP 3 PHASE TEFC  40 (4 WORDS FOUND)
   


C:\DM\Sku.mdb
"SELECT fldID, MFRNUM, MFRNAME, DESC FROM tblSku ORDER BY DESC"
"SELECT fldDId ,fldMfgname, fldMfrnum, fldDescription, fldScore FROM tblData ORDER BY fldDescription"
Start Free Trial