I have the select below that is giving me a small problem.  what it does is pull a list of supporters
for a specific missionary in the fcats table along with the gift information (if any) of people who donated to that missionary.  The
problem is if someone is on the list for a missionary but have not given to that particular missionary but have given to another one
they do not show up.  This means they have entries in the detail table with a desigcode other than the missionary's.   People who
are listed as supporters but have not yet given anything show up fine it is only the ones who gave to others that are missing.  Any
ideas?

	strTL = "SELECT n.name1_ref, n.account, f.code, n.first, n.last_name, n.org_name, n.spouse, n.street, "
	strTL = strTL + "n.street2, n.city, n.state, n.zip, n.country, n.intro_date, n.email, "
	strTL = strTL + "sum(case when d.amount is not null and d.amount > 0 then d.amount else 0 end) as gift, "
	strTL = strTL + "case when len(p.pledge_ref) < 10 or p.pledge_ref is null then 'S List' else 'Pledge' end as list "
	strTL = strTL + "FROM fcats f join fcatinfo fc on f.fcat_ref = fc.fcat_ref "
	strTL = strTL + "join name1 n on fc.name1_ref = n.name1_ref "
	strTL = strTL + "left join gifts g on g.name1_ref = fc.name1_ref "
	strTL = strTL + "left join detail d on d.check_ref=g.gift_ref "
	strTL = strTL + "left Join Designat ds on ds.desig_ref=d.desig_ref "
	strTL = strTL + "left join pledge p on p.name1_ref = fc.name1_ref "
	strTL = strTL + "where f.code = "&strHCcode&" and (ds.desigcode = '"&strHCcode&"' or ds.desigcode is null) "
	strTL = strTL + "and (d.gifttype < 2 or d.gifttype is null) "
	strTL = strTL + "group by n.account, n.first, n.last_name, n.org_name, "
	strTL = strTL + "n.street, n.street2, n.city,n.state, n.zip,n.country, n.intro_date, n.email, "
	strTL = strTL + "n.name1_ref, f.code, n.spouse, p.pledge_ref "
	strTL = strTL + "order by list, last_name, first, n.org_name, account "

Tom Achtenberg
Developer, Information Technologies
Food for the Hungry / US
(xxx) xxx-xxxx  (voice)
(xxx) xxx-xxxx  (fax)
TomA@f...