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.
>