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!!!