I have the following code below. It does fine until I try to put the two tables together. It always leaves out a table that is contained in the table on the right of the join condition. I have tried every thing but it does it. I hvae tried the "ALL", "LEFT", "INNER" and "RIGHT" outer joins to no avail. Could you folks tell me what I am doing wrong. An idea just came to mind. Maybe I should use the FORCE command to insure the table on the left is evaluated first?
Any ideas?
Thanks in advance for your help!
Randy
Select all hitrate1.settdate, hitrate1.portfolio, hitrate1.bankname, ;
COUNT(ISNULL(newmid)) AS nofind ;
FROM hitrate1 ;
WHERE ISNULL(newmid)=.T. ;
ORDER BY bankname, settdate ;
GROUP BY bankname, settdate ;
INTO TABLE hitrate2
Use IN hitrate1
Use IN hitrate2
Select all hitrate1.settdate, hitrate1.portfolio, hitrate1.bankname, ;
COUNT(!ISNULL(newmid)) AS FIND ;
FROM hitrate1 ;
WHERE ISNULL(newmid)=.F. ;
ORDER BY bankname, settdate ;
GROUP BY bankname, settdate ;
INTO TABLE hitrate3
Use IN hitrate1
Use IN hitrate3
Select all ach_final.settdate, ach_final.portfolio, ach_final.bankname, ;
COUNT(nsf_fee) AS numach ;
FROM ach_final ;
WHERE BETWEEN(ach_final.settdate, dStartDate, dEndDate) AND nsf_fee#20.00 ;
ORDER BY bankname, settdate ;
GROUP BY bankname, settdate ;
INTO TABLE achrate
Use IN achrate
Select all hitrate2.*, hitrate3.FIND, (hitrate3.FIND+hitrate2.nofind) AS totrecords, ;
(hitrate3.FIND/(hitrate3.FIND+hitrate2.nofind))*100 AS findrate ;
FROM hitrate2 FULL OUTER JOIN hitrate3 ;
ON (hitrate2.portfolio) = (hitrate3.portfolio) ;
AND (hitrate2.settdate) = (hitrate3.settdate) ;
where (hitrate2.portfolio) = (hitrate3.portfolio) ;
AND (hitrate2.settdate) = (hitrate3.settdate) ;
ORDER BY hitrate2.bankname, hitrate2.portfolio, hitrate2.settdate ;
GROUP BY hitrate2.bankname, hitrate2.portfolio, hitrate2.settdate ;
INTO TABLE hitrate4
Thanks in advance for your help! :-)
Randall L. Johnson