>>>Hi there -
>>>
>>>I am working to convert a VFP project to SQL Server. I have the following select statement that seems to be working OK.
>>>

select a.element, a.subelement, a.elementnum, a.Lead, a.TotalCost, a.OtherFunds, a.Budget, a.Period,
>>>convert(integer,SUM(l.amount)/1000) as expended
>>>from RTA_Elements a
>>>left join TIP_Projects b on a.ElementNum = convert(integer,substring(b.RtaID,5,2))
>>>left join TIP_Ledger l on b.TipID = l.tipid and l.Fundtype = 'RTA'
>>>group by a.element, a.subelement, a.elementnum, a.Lead, a.TotalCost, a.OtherFunds, a.Budget, a.Period
>>>order by elementnum

>>>
>>>However I need one more aggregate field from another table. So when I modify it like this
>>>

>>>select a.element, a.subelement, a.elementnum, a.Lead, a.TotalCost, a.OtherFunds, a.Budget, a.Period,
>>>convert(integer,SUM(l.amount)/1000) as expended, sum(p.amount) as programmed
>>>from RTA_Elements a
>>>left join TIP_Projects b on a.ElementNum = convert(integer,substring(b.RtaID,5,2))
>>>left join TIP_Ledger l on b.TipID = l.tipid and l.Fundtype = 'RTA'
>>>left join TIP_ApprovedFunding p ON b.TipID = p.TipID and p.Fundtype = 'RTA'
>>>group by a.element, a.subelement, a.elementnum, a.Lead, a.TotalCost, a.OtherFunds, a.Budget, a.Period
>>>order by elementnum

the values for expended (which were correct before) and the values for programmed, come out wrong.
>>>
>>>Can someone suggest what I'm doing wrong here?
>>>Thanks
>>
>>
>>Nothing,
>>Remove aggregate functions and GROUP BY and you will see what happened.
>>Some records will be doubled just becuase you added another table.
>>Maybe you have more than one record for TipID and Fundtype = 'RTA' in TIP_ApprovedFunding
>
>Borislav - You are right. The relationships are:
>RTA_Element is parent
>TIP_Projects is child
>TIP_ApprovedFunding is child of TIP_Projects
>TIP_Ledger is child of TIP_Projects
>
>Is there no solution to putting this together? My original code had one of the columns derived by a second select but I can't seem to make that work either.



Use so called derived tables and do the calculations there.
Try this:

select a.element,
       a.subelement,
       a.elementnum,
       a.Lead,
       a.TotalCost,
       a.OtherFunds,
       a.Budget,
       a.Period,
       convert(integer,l.amount/1000) as expended,
       p.amount as programmed
from RTA_Elements a
left join TIP_Projects b on a.ElementNum = convert(integer,substring(b.RtaID,5,2))
left join (SELECT tipid,
                  SUM(Ammount) AS Ammount
            FROM TIP_Ledger
            WHERE Fundtype = 'RTA'
            GROUP BY TipId) l on b.TipID = l.tipid
left join (SELECT tipid,
                  SUM(Ammount) AS Ammount
            FROM TIP_ApprovedFunding
            WHERE Fundtype = 'RTA'
            GROUP BY TipId) p on b.TipID = p.tipid
order by a.elementnum

This is NOT TESTED.
To test it I need example data and desired result from it.