Hi Martina -
You can think of RTA_Elements as a parent, Tip_Projects as a child, Tip_Ledger and Tip_Approved funding as grandchildren. All the relations are 1 to many.

>How mayn rows are in TIP_ApprovedFunding for one record in RTA_Elements?
>
>MartinaJ
>
>>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