SELECT apt.apt, apt.mkt_rent as mkt_rent, IIF(ISNULL(last_rent),'-',MAX(last_rent)) as last_Rent, IIF(ISNULL(dt_vacate),'-',DTOC(MAX(dt_vacate))) as dt_vacate,; apt.type as type, IIF(ISNULL(dt_vacate) AND ISNULL(last_rent),'Not found in archive file',SPACE(26)) as reason ; FROM archive; RIGHT OUTER JOIN apt ON archive.apt=apt.apt ; WHERE apt.apt NOT in (select apt FROM cross WHERE NOT EMPTY(last)) ; GROUP BY APT.APT ; INTO CURSOR c_vacancylist NOFILTER FLUSH