>I think I am going braindead trying to figure this out.
>
>I have to compare 47,000 records (I'll call BM) to 8,500 records (I'll call SD) to find all the possible matches between them. There maybe:
>a) no match in SD for each record in BM
>b) maybe a single match in SD for each record in BM
>c) maybe multiple matches in SD for each record in BM
>
>The end result is I need a table that contains each record in BM listing all the potential matches to SD (or no match all).
>
>I have 3 SQL statements that do the match and work independantly. I have tested each and they do as required. They are:
>
>--------------------------------------
>** read EXACT matches
>select * from bmdata left join sddmdata ;
> on left(&bmzip,5)=left(sddmdata.sdzip,5) ;
> where !empty(trim(sddmdata.sdaddr)) and ;
> upper(trim(&bmlname))=upper(trim(sddmdata.sdlname)) and ;
> upper(trim(&bmaddr))=upper(trim(sddmdata.addr)) into cursExact
>
>** read PROBABLE matches
>select * from bmdata left join sddmdata ;
> on left(&bmzip,5)=left(sddmdata.sdzip,5) ;
> where !empty(trim(sddmdata.sdaddr)) and ;
> upper(trim(&bmaddr))=upper(trim(sddmdata.addr)) into cursProb
>
>** read POSSIBLE matches
>select * from bmdata left join sddmdata ;
> on left(&bmzip,5)=left(sddmdata.sdzip,5) ;
> where !empty(trim(sddmdata.sdaddr)) and ;
> val(trim(&bmaddr))=val(trim(sddmdata.addr)) into cursPoss
>-----------------------------------------------------
>
>I believe my next step (or modifications to the above) is to combine these into a single cursor. The key here is hierachy. Keep in mind, I have stored the RECNO for each BM record into the cursors:
>
>- if a BM record exists in cursExact then keep that record and ignore any matches in cursProb & cursPoss for that BM record number
>- if a BM record exists in cursProb and not in cursExact, keep that record and ignore any matches in cursPoss for that BM record number
>- if a BM record exists in cursPoss and not in cursProb or cursExact, keep that record for that BM record number
>
>
>
>When I have the ending cursor, I need to then finally compare the cursor with the original BM table:
>
>- if the BM record (by recno()) has no match in the end cursor, then keep the BM record
>- if the BM record (by recno()) has at least one match in the end cursor, delete the orginal record in BM and add ALL the matching records from the end cursor.
>
>
>No matter what, the ending table (BM) will have at least the original records with no matches, or most likely, a list of all the non-matches and matches. Those records will then be used in a grid grouping on the original BM number.
>
>
>
>
>I hope this all makes sense - probably why it is driving me nuts!!! HELP!!!

Peter,
Have you ever thought of nonSQL processing. It might be easier and faster.

lnAddr=fsize(bmaddr,'BMData')
lnSDLName=fsize(bmlname,'BMData')
use SDData in 0 again alias ExMatch
index on left(sdzip,5)+padr(addr,lnAddr)+padr(sdlname,lnSDLName) tag ExMatch
use SDData in 0 again alias ProbMatch
* index
use SDData in 0 again alias PossMatch
* index
select BMData
set relation to ExactExpr into ExMatch, ;
ProbExpr into ProbMatch, ;
PossExpr into PossMatch

scan
 do case
   case !eof('ExMatch') && Exactmatch found 
*
   case !eof('ProbMatch') && Probable match found 
*
   case !eof('PossMatch') && Possible match found 
*
   Otherwise && No match
endscan

Cetin