I spent some time looking at what you're trying to do. I ignored the AchRate table since it isn't used in the final SQL statement. I've written one SQL statement that replaces the first, second and last SQL statements in your question.
SELECT SettDate, Portfolio, Bankname, SUM(IIF(!ISNULL(NewMID),1,0)) AS Find, ;
COUNT(*) AS TotRecords, ;
SUM(IIF(!ISNULL(NewMID),1,0)) / COUNT(*) * 100 AS FindRate ;
FROM HitRate1 ;
GROUP BY Bankname, SettDate ;
INTO CURSOR Results
A few notes:
When you include a GROUP BY clause, the results are ordered by the fields you specify, so you don't need to include and ORDER BY clause if you are ordering by the same fields.
You should always use INTO CURSOR instead of INTO TABLE, unless you really need a file on the disk. INTO CURSOR is faster and if you happen to forget to close it, the form (or application if using the Default Data Session) will close it for you when the form is closed. If you use INTO TABLE, the tempoaray tables have a tendancy to accumulate on the disk.
> 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
Brien R. Givens
Brampwood Systems