>I do something like this:
>
>
>m.dStartDate={10/01/2000}
>m.dEndDate={10/31/2000}
>
>SELECT prenotes.bankname, prenotes.portfolio, ;
>SUM(IIF(!isnull(mid),1,0)) AS foundit, ;
>SUM(IIF(isnull(mid),1,0)) AS notfound, ;
> COUNT(prenotes.mid) AS tot_prnote ;
> FROM prenotes ;
> WHERE BETWEEN(prenotes.settdate, m.dStartDate, m.dEndDate) ;
> GROUP BY prenotes.bankname ;
> INTO CURSOR mis_prenotes
>
>
>I get a record with bankname,foundit,notfound,tot_prnote.
>
>If there are more than one bank I get a record for each bank. If a bank is missing, I get nothing. I want to get a record for all the banks that are in the table regardless of there being a numbers for it or not. For example, one record would have bank A with numbers in the fileds and Bank B would have all zeroes in it. Is this possible?
>
>Thank you in advance for your help! :-)
>randall



You can do a UNION of your original query and one that finds all records that don't match your selection criteria. I think the following will also work although it may be a bear on a big table

SELECT prenotes.bankname, prenotes.portfolio, ;
SUM(IIF(BETWEEN(prenotes.settdate, m.dStartDate, m.dEndDate) AND !isnull(mid),1,0)) AS foundit, ;
SUM(IIF(BETWEEN(prenotes.settdate, m.dStartDate, m.dEndDate) AND isnull(mid),1,0)) AS notfound, ;
SUM(IIF(BETWEEN(prenotes.settdate, m.dStartDate, m.dEndDate),1,0)) AS tot_prnote ;
FROM prenotes ;
GROUP BY prenotes.bankname ;
INTO CURSOR mis_prenotes