Sergey,

Thanks for the suggestion. I went with David's fix to use 3 joins instead. I tried the IIF() solution but my script would not run. This is actually being run in a 3rd party SQL Scripting engine, not in VFP.

Thank you.

Elgin

>Try

>SELECT
>		tn.scode, tn.sLastname,
>		SUM(IIF(hchargecode="RENT", cr.dEstimated, 0)) AS Rent,
>		SUM(IIF(hchargecode="CAM", cr.dEstimated, 0)) AS Cam,
>		SUM(IIF(hchargecode="TAX", cr.dEstimated, 0)) AS Tax
>	FROM tenant tn
>	LEFT JOIN camrule cr ON tn.hmyperson = cr.htenant
>        WHERE cr.SomeDate BETWEEN ldStartDate AND ldEndDate
>	GROUP BY 1,2	
>

>>I have two tables, TENANT t and CAMRULE cr. I'm doing a LEFT OUTER JOIN camrule ON t.hmyperson = cr.htenant. I am wanting to SELECT t.scode, t.slastname from my tenant table and cr.destimated from my camrule table. Here's the catch. In my camrule table, I have 'rent' type camrules, 'cam' type camrules, and 'tax' type camrules. These camrule types are stored in a third table called CHARGTYP. The camrules table has a field, hchargecode that is the pointer back to the chargtyp table for each type of charge code.
>>
>>In the camrule table there may be more than one record of each type for each tenant. In other words, one tenant may have three RENT type camrules and three CAM type camrules that are for different date ranges.
>>
>>In my result set I want the varying types of camrules that match a certain date criteria to appear on the same record line as separate columns. For example I want the result like this:
>>
><snip>
>>
>>I tried doing a SELECT statement for each of the fields, but in some cases I may get more than one record returned, and that's not acceptable when doing a subquery.
>>